Business Analytics Help !
DeMario34YO19_Excel_Ch03_Prepare_Wedding_Instructions.docx
Grader - Instructions Excel 2019 Project
YO19_Excel_Ch03_Prepare_Wedding
Project Description:
Clint Keller and Addison Ryan have just booked a wedding at Painted Paradise Resort & Spa. When requested by a happy couple, the Turquoise Oasis Spa coordinates a variety of events including spa visits, golf massages, and gift baskets made up of various spa products. Given the frequency of wedding events at the Turquoise Oasis Spa, Meda Rodate has asked for your assistance in modifying an Excel workbook that can be used and reused to plan these events in the future.
Steps to Perform:
Step |
Instructions |
Points Possible |
1 |
Start Excel. Download and open the file named Excel_Ch03_Prepare_Wedding.xlsx. Grader has automatically added your last name to the beginning of the filename. Save the file to a location where you are storing your files. |
0 |
2 |
To help the spa manager design a workbook to become more efficient in planning for wedding events, you will edit the Wedding workbook. On the GiftBaskets worksheet, in cell F9, use AutoSum to calculate the total items in Basket 1. Copy the formula in cell F9 through cell F11. |
0.3 |
3 |
To save yourself time, you may want to consider using absolute referencing so you can copy formulas to other cells within the workbook. In cell B15, type =B14-(B14*$E$3) Copy the formula through cell E15. |
0.4 |
4 |
In certain worksheets, mixed referencing is the most efficient way to save yourself time when creating a formula you wish to copy to other cells within the worksheet. In cell B18, type =B9*B$15 Copy the formula in cell B18 through cell B20. Copy cell range B18:B20 through the range E18:E20. |
0.4 |
5 |
You found an error in the worksheet. Since formulas have been created to perform calculations in the worksheet, changes to data will automatically be reflected throughout the worksheet. In cell E14, change the price of Soothing Bath Salts to 9.99 |
0.2 |
6 |
The use of named ranges enables a developer to quickly develop formulas that make sense. It also increases the readability of formulas to other individuals who are using the same workbook. Create a named range BasketSubtotals for the cell range C23:C25. |
0.2 |
7 |
On the GiftBaskets worksheet, click the Name Box arrow, and then click BasketsRequested. Notice that the range selected is B24:B26. This is the incorrect range. Edit the BasketsRequested named range so that it refers to cell range B23:B25. |
0.2 |
8 |
In cell F24, use the SUM function to add up all the values in the BasketSubtotals named range. Use the range name in the formula. |
0.3 |
9 |
You can quickly assign named ranges in Excel. Using the cell range A18:E20, create named ranges using the values in the left column for the range names. |
0.2 |
10 |
Apply the named ranges just created to the formulas in cell range F18:F20. |
0.2 |
11 |
The number of days spent by wedding parties at the Painted Paradise Resort & Spa had previously been calculated by using decimal values based on check-in and checkout times. You will change this data to display as whole numbers of days. On the WeddingSummary worksheet, in cell C7, enter a formula to round the number in cell B7 down to the nearest integer. Copy the formula through cell C37. Format cell range C7:C37 with zero decimal places. |
0.28 |
12 |
The value of merchandise that was returned after the wedding took place is listed in both positive and negative values. You will correct this so all values are displayed as a positive number. In cell E7, enter a function that returns the absolute value of the returned merchandise in cell D7. Copy the function through cell E37. |
0.3 |
13 |
In cell C1, edit the function to round the result to the nearest penny. |
0.3 |
14 |
You want to count the number of members who have scheduled weddings. Since the data in column A is text, you cannot use the COUNT function. In cell C2, use a count function that will calculate the number of wedding parties served by counting the wedding party names. |
0.48 |
15 |
Next, you want to count the number of wedding guests who were also spa members. In cell C3, use a count function to calculate the number of spa members scheduling weddings by counting the numbers, and not the Null values, in column G. |
0.48 |
16 |
On the SpaServices worksheet, in cell B1, use a function to return the current date. |
0.16 |
17 |
In cell D5, use the DATEDIF function to calculate the age of the guest in years, using date of birth found in C5 and today’s date in cell B1. Use absolute referencing where appropriate and then copy the formula through cell D22. |
0.4 |
18 |
In the SpaServices worksheet, the text that was entered for guest names is in all capital letters. You want to change this to the proper format of only the first letter of each word being capitalized. In cell B5, use a text function to change the data in A5 to proper case. Copy the formula though cell B22. |
0.48 |
19 |
Next you want to determine if the wedding party qualifies for a price discount. On the WeddingFinancing worksheet, in cell B7, enter a lookup function to retrieve appropriate percentage discount from the table array in cell range E4:F8 based on the total cost of the event in cell B3. |
0.48 |
20 |
When working with potential wedding parties, it is important they understand what the monthly payment will be if they will be using the financing option. In cell B10, use the PMT function to calculate the monthly payment amount necessary to pay off the amount borrowed in cell B9, at the end of the term in cell B6, using the interest rate in cell B5. Be sure the result of the PMT function is positive. |
0.48 |
21 |
In cell B8, determine if the guest's event is eligible for a discount. To determine if a guest will receive a discount, the total cost of the event must be greater than or equal to $20,000. If the event cost is greater than or equal to $20,000, then the cost of the event should be multiplied by the discount calculated in cell B7. If the event cost is not greater than or equal to $20,000 then the result will be a 0 (zero) discount. |
0.48 |
22 |
On the Commission worksheet, in cell B4, troubleshoot and edit the function so that it retrieves the correct value of 5.00%. |
0.2 |
23 |
In cell B8, troubleshoot and edit the function so that it retrieves the correct value of $1,250 for a Manager. |
0.2 |
24 |
You want to analyze the wedding party stays at the resort by determining the total number of wedding parties who spent four days at the resort. On the SalesAnalysis worksheet, in cell B3, enter a formula that counts the number of wedding parties in cells B7:B37 if they spent four days at the spa, cell A3. |
0.48 |
25 |
You want to know the total number of wedding parties if the party spent four days at the resort and are non-spa (Null) members. In cell B4, enter a formula to count the number of parties if they stayed 4 days (cell A3) and are non-spa (Null) members (cell A4). |
0.48 |
26 |
Next, you want to determine the average amount spent at the spa for parties who spent 4 days. In cell C3, enter a formula to calculate the average amount spent by parties if they spent 4 days (cell A3) at the spa. |
0.48 |
27 |
Next, you will determine if there is a significant difference between the average amount spent at the spa and the average amount spent at the spa for non-spa (Null) members. To gather this information, you will need to create a formula based on two sets of criteria. In cell C4, enter a function to calculate the average if the guest stayed at least 4 nights and is a non-spa (Null) customer. |
0.48 |
28 |
Now you want to determine the total amount spent at the spa if the guest stayed for four days. In cell D3, enter a formula to calculate the total amount spent at the spa if the guest stayed for four days. |
0.48 |
29 |
To finish the analysis of the spa usage, you want to determine the total spa sales if four whole days were spent at the spa and the guests were non-spa (Null) members. In cell D4, enter a function that sums the total sales if the customer stayed for 4 days and was a non-spa (Null) member. |
0.48 |
30 |
Save and close Excel_Ch03_Prepare_Wedding.xlsx. Exit Excel. Submit the file as directed. |
0 |
Total Points |
10 |
Created On: 02/13/2020 1 YO19_Excel_CH03_Prepare - Wedding 1.1
McDonald_Excel_Ch03_Prepare_Wedding.xlsx
GiftBaskets
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 |