Business Analytics Help !

profileDeMario34
McDonald_YO19_Excel_Ch03_Prepare_Wedding1.zip

YO19_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

Excel_Ch03_Prepare_helpful_image.jpg