Excel Chapter 3 Prepare Case - Wedding
abparsley
Parsley_Excel_Ch03_Prepare_Wedding1.xlsxGiftBaskets
| Quote for Customized Gift Baskets | |||||
| Customized Baskets | Description of Custom Basket | Bulk Discount | 10% | ||
| Basket 1 | Attendees' Unwind and Renew | ||||
| Basket 2 | Wedding Party Pamper Yourself | ||||
| Basket 3 | Parents of the Bride and Groom Stress Free | ||||
| # of Items per Basket | Lavender Candle | Chamomile Body Lotion | Foot Renew Cream | Soothing Bath Salts | Items Per Basket |
| Basket 1 | 2 | 1 | 1 | 0 | |
| Basket 2 | 2 | 2 | 2 | 2 | |
| Basket 3 | 3 | 2 | 2 | 4 | |
| Item Price List | Lavender Candle | Chamomile Body Lotion | Foot Renew Cream | Soothing Bath Salts | |
| Retail Price | 4.99 | 7.99 | 5.99 | 8.99 | |
| Price After Bulk Discount | |||||
| Price Per Basket | Lavender Candle | Chamomile Body Lotion | Foot Renew Cream | Soothing Bath Salts | Per Basket Price |
| Basket 1 | - 0 | ||||
| Basket 2 | - 0 | ||||
| Basket 3 | - 0 | ||||
| Basket Orders | Baskets Requested | Basket Subtotals | |||
| Basket 1 | 50 | $ - 0 | Total Baskets in Order | 10 | |
| Basket 2 | 8 | $ - 0 | Total Billing Amount | ||
| Basket 3 | 2 | $ - 0 | Prepared for: | Keller - Ryan Wedding | |
| *To customize for a different order only modify the blue shaded boxes. |
WeddingSummary
| Average Amount Spent at Spa | 874.6993548387 | |||||
| Number of Weddings | ||||||
| Spa Members Scheduling Weddings | ||||||
| Wedding Party Name | Number of Days Spent | Whole Days Spent | Returned Merchandise | Value of Returned Merchandise | Amount Spent at Spa | Spa Member |
| Arroyo | 1.52 | -37.34 | $ 1,215.63 | 1 | ||
| Proctor | 2.54 | 41.12 | $ 720.60 | 1 | ||
| Minor | 4.32 | -12.7 | $ 455.78 | 1 | ||
| Pearce | 4.52 | 13.97 | $ 1,309.51 | Null | ||
| Shea | 2.50 | 18.53 | $ 1,345.57 | 1 | ||
| Galvan | 4.00 | 11.09 | $ 1,046.69 | 1 | ||
| Crowe | 2.31 | 36.64 | $ 1,125.53 | 1 | ||
| Meeks | 2.45 | 47.7 | $ 1,068.90 | 1 | ||
| Kendrick | 3.65 | 10.24 | $ 604.95 | Null | ||
| Mayfield | 4.45 | 7.88 | $ 299.60 | Null | ||
| Kendall | 3.11 | 21.78 | $ 353.74 | Null | ||
| Archer | 3.15 | 37.51 | $ 923.56 | 1 | ||
| Holley | 4.19 | 34.36 | $ 1,285.26 | Null | ||
| Boucher | 4.10 | 5.79 | $ 1,157.07 | 1 | ||
| Childs | 1.63 | -2.47 | $ 206.77 | 1 | ||
| Rankin | 4.50 | -12.66 | $ 781.30 | Null | ||
| Lozano | 2.55 | 49.74 | $ 1,070.70 | Null | ||
| Odell | 4.42 | -18.66 | $ 867.45 | Null | ||
| Bland | 2.72 | -17.86 | $ 267.16 | 1 | ||
| Rouse | 1.65 | 24.98 | $ 1,330.38 | 1 | ||
| Haas | 4.40 | 23.12 | $ 899.77 | Null | ||
| Swain | 1.16 | -7.23 | $ 1,479.14 | Null | ||
| Oconnell | 2.64 | 1.11 | $ 953.20 | 1 | ||
| Dougherty | 3.50 | -0.46 | $ 556.17 | Null | ||
| Andersen | 3.58 | -19.03 | $ 644.50 | Null | ||
| Wang | 1.91 | -3.15 | $ 606.30 | 1 | ||
| Cowan | 1.28 | 40.74 | $ 955.27 | Null | ||
| Elder | 2.00 | -16.6 | $ 760.12 | 1 | ||
| Shirley | 2.59 | 31.05 | $ 870.85 | 1 | ||
| Hartley | 3.60 | 25.91 | $ 1,146.79 | 1 | ||
| Felix | 4.57 | 30.66 | $ 807.42 | Null |
SpaServices
| Today's Date | |||
| Spa Service Requests | |||
| Guest Name | Proper Guest Names | Date Of Birth | Age |
| OLIVIA GARCIA | 2/20/88 | ||
| MIA RAMOS | 11/11/72 | ||
| ISABELLA HUDSON | 10/19/75 | ||
| RHONDA GRIFFITH | 1/2/69 | ||
| STEVEN LUCAS | 2/26/70 | ||
| ELIZABETH ANDERSON | 4/14/77 | ||
| JESSICA MONTGOMERY | 10/6/69 | ||
| ELLA WEST | 5/26/78 | ||
| JOSEPH WALLACE | 6/19/86 | ||
| JOSHUA PIERCE | 5/12/69 | ||
| RYAN JENNINGS | 6/5/71 | ||
| JUDY BROWN | 11/8/67 | ||
| HANNAH ROWE | 5/8/74 | ||
| JILLIAN MCDONALD | 6/9/76 | ||
| JUN BARKER | 8/22/74 | ||
| JERALDO RIVERA | 3/9/85 | ||
| JANE MONTGOMERY | 5/12/86 | ||
| SUSAN BURGESS | 6/14/64 |
WeddingFinancing
| Financing Calculator | |||||
| Credit Score | 725 | ||||
| Total Cost of Event | $ 37,000.00 | Percent Discount | |||
| Down payment | $ 3,500.00 | 0 | 0.00% | ||
| Rate (Annualized) | 4.00% | 20000 | 1.00% | ||
| Term (in Years) | 2 | 30000 | 5.00% | ||
| Percentage Discount | 40000 | 7.50% | |||
| Amount of Discount | 50000 | 11.00% | |||
| Total amount borrowed | $ 33,500.00 | ||||
| Monthly Payment |
Commission
| Event Earnings Calculator | |||||
| Cost of Event | $17,000.00 | Cost of Event | Commission Percentage | ||
| Commission Percentage | ERROR:#N/A | 0 | 0.00% | ||
| Commission Earned | ERROR:#N/A | 5000 | 1.00% | ||
| 10000 | 3.00% | ||||
| Employee Level | Manager | 15000 | 5.00% | ||
| Base Event Pay | $ 750 | 20000 | 8.00% | ||
| Total Earnings | ERROR:#N/A | Level | Base Event Pay | ||
| Entry | $ 500 | ||||
| Consultant | $ 750 | ||||
| Senior Consultant | $ 1,000 | ||||
| Manager | $ 1,250 | ||||
| Senior Manager | $ 1,500 |
SalesAnalysis
| Spa Sales Analysis | ||||
| Guest Criteria | Number of Spa Customers | Average Spa Sales | Spa Sales | |
| 4 | ||||
| Null | ||||
| Wedding Party Name | Whole Days Spent | Value of Returned Merchandise | Amount Spent at Spa | Spa Member |
| Arroyo | 1 | $ 37.34 | $ 1,215.63 | 1 |
| Proctor | 2 | $ 41.12 | $ 720.60 | 1 |
| Minor | 4 | $ 12.70 | $ 455.78 | 1 |
| Pearce | 4 | $ 13.97 | $ 1,309.51 | Null |
| Shea | 2 | $ 18.53 | $ 1,345.57 | 1 |
| Galvan | 4 | $ 11.09 | $ 1,046.69 | 1 |
| Crowe | 2 | $ 36.64 | $ 1,125.53 | 1 |
| Meeks | 2 | $ 47.70 | $ 1,068.90 | 1 |
| Kendrick | 3 | $ 10.24 | $ 604.95 | Null |
| Mayfield | 4 | $ 7.88 | $ 299.60 | Null |
| Kendall | 3 | $ 21.78 | $ 353.74 | Null |
| Archer | 3 | $ 37.51 | $ 923.56 | 1 |
| Holley | 4 | $ 34.36 | $ 1,285.26 | Null |
| Boucher | 4 | $ 5.79 | $ 1,157.07 | 1 |
| Childs | 1 | $ 2.47 | $ 206.77 | 1 |
| Rankin | 4 | $ 12.66 | $ 781.30 | Null |
| Lozano | 2 | $ 49.74 | $ 1,070.70 | Null |
| Odell | 4 | $ 18.66 | $ 867.45 | Null |
| Bland | 2 | $ 17.86 | $ 267.16 | 1 |
| Rouse | 1 | $ 24.98 | $ 1,330.38 | 1 |
| Haas | 4 | $ 23.12 | $ 899.77 | Null |
| Swain | 1 | $ 7.23 | $ 1,479.14 | Null |
| Oconnell | 2 | $ 1.11 | $ 953.20 | 1 |
| Dougherty | 3 | $ 0.46 | $ 556.17 | Null |
| Andersen | 3 | $ 19.03 | $ 644.50 | Null |
| Wang | 1 | $ 3.15 | $ 606.30 | 1 |
| Cowan | 1 | $ 40.74 | $ 955.27 | Null |
| Elder | 1 | $ 16.60 | $ 760.12 | 1 |
| Shirley | 2 | $ 31.05 | $ 870.85 | 1 |
| Hartley | 3 | $ 25.91 | $ 1,146.79 | 1 |
| Felix | 4 | $ 30.66 | $ 807.42 | Null |
Documentation
| Create Date | By Whom | Description | Workbook Name |
| 9/18/22 | Meda Rodate | Wedding planning workbook | Excel_Ch03_Prepare_Wedding.xlsx |
| Mod. Date | By Whom | Mod. Description | Last Version Backup Name Author: VERSION BACKUP NAME : Before modifying any worksheet, save the original workbook with the following name format: Original name_yyyymmdd |
| Create Date | Sheet Name | Creator | Purpose |
| 9/18/22 | GiftBaskets | Meda Rodate | |
| 9/18/22 | WeddingSummary | Meda Rodate | |
| 9/18/22 | SpaServices | Meda Rodate | |
| 9/18/22 | WeddingFinancing | Meda Rodate | |
| 9/18/22 | Commission | Meda Rodate |
