YO19_Excel_Cho3_Prepare_Wedding Project Description: Clint Keller and Addison Ryan have just booked a wedding at Painted Paradise Resort and 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: Points Step Instructions Possible Start Excel. Download and open the file named Excel_Cho3_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 1 0 3 2 4 3 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. 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. 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 You found an error in the worksheet Since formulas have been created to perform calculations 2 in the worksheet, changes to data will automatically be reflected throughout the worksheet. 4 5 rin ni Santhin Rath Salle tn 999 Copy 11, 3 4 4 5 6 To save yourself time, you may want to consider using absolute referencing so you can copy 4 formulas to other cels within the workbook. In cell B15, type=B14 (814$E$3) Copy the formula through cell E15. 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 You found an error in the worksheet. Since formulas have been created to perform calculations 2 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 The use of named ranges enables a developer to quickly develop formulas that make sense It 2 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. On the GiftBaskets worksheet, click the Name Box arrow, and then click Baskets Requested. 2 Notice that the range selected is B24B26. This is the incorrect range. Edit the Baskets Requested named range so that it refers to cell range B23:B25 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 You can quickly assign named ranges in Excel 2 Using the cell range A18:E20, create named ranges using the values in the left column for the range names Apply the named ranges just created to the formulas in cell range F18:F20. 2 The number of days spent by wedding parties at the Painted Paradise Resort & Spa had 28 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 7 3 8 9 10 11 12 On the Wedding Summary 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 cel range C7C37 with zero decimal places. 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 3 13 In cell C1, edit the function to round the result to the nearest penny. 3 14 4.8 15 4.8 16 1.6 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 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. On the SpaServices worksheet, in cell B1, use a function to return the current date. 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. 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 Next you want to determine if the wedding party qualifies for a price discount 17 4 18 4.8 19 4.8 On the Waristina Financing Moreheat in call R antara lankan function to retrina tha 19 Next you want to determine if the wedding party qualifies for a price discount 4.8 On the Wedding Financing worksheet, in cell B7, enter a lookup function to retrieve the appropriate percentage discount from the table array in cell range E4 F8 based on the total cost of the event in cell B3. 20 When working with potential wedding parties, it is important they understand what the monthly 4.8 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. 21 4.8 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 22 2 23 2 24 4.8 25 4.8 26 4.8 On the Commission worksheet, in cell B4, troubleshoot and edit the function so that it retrieves the correct value of 5.00%. In cel B8, troubleshoot and edit the function so that it retrieves the correct value of $1,250 for a Manager 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.837 if they spent four days at the spa, cell A3 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). 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 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 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 (cell A3). 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 27 4.8 28 4.8 29 4.8 YO19_Excel_Cho3_Prepare_Wedding Project Description: Clint Keller and Addison Ryan have just booked a wedding at Painted Paradise Resort and 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: Points Step Instructions Possible Start Excel. Download and open the file named Excel_Cho3_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 1 0 3 2 4 3 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. 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. 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 You found an error in the worksheet Since formulas have been created to perform calculations 2 in the worksheet, changes to data will automatically be reflected throughout the worksheet. 4 5 rin ni Santhin Rath Salle tn 999 Copy 11, 3 4 4 5 6 To save yourself time, you may want to consider using absolute referencing so you can copy 4 formulas to other cels within the workbook. In cell B15, type=B14 (814$E$3) Copy the formula through cell E15. 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 You found an error in the worksheet. Since formulas have been created to perform calculations 2 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 The use of named ranges enables a developer to quickly develop formulas that make sense It 2 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. On the GiftBaskets worksheet, click the Name Box arrow, and then click Baskets Requested. 2 Notice that the range selected is B24B26. This is the incorrect range. Edit the Baskets Requested named range so that it refers to cell range B23:B25 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 You can quickly assign named ranges in Excel 2 Using the cell range A18:E20, create named ranges using the values in the left column for the range names Apply the named ranges just created to the formulas in cell range F18:F20. 2 The number of days spent by wedding parties at the Painted Paradise Resort & Spa had 28 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 7 3 8 9 10 11 12 On the Wedding Summary 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 cel range C7C37 with zero decimal places. 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 3 13 In cell C1, edit the function to round the result to the nearest penny. 3 14 4.8 15 4.8 16 1.6 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 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. On the SpaServices worksheet, in cell B1, use a function to return the current date. 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. 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 Next you want to determine if the wedding party qualifies for a price discount 17 4 18 4.8 19 4.8 On the Waristina Financing Moreheat in call R antara lankan function to retrina tha 19 Next you want to determine if the wedding party qualifies for a price discount 4.8 On the Wedding Financing worksheet, in cell B7, enter a lookup function to retrieve the appropriate percentage discount from the table array in cell range E4 F8 based on the total cost of the event in cell B3. 20 When working with potential wedding parties, it is important they understand what the monthly 4.8 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. 21 4.8 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 22 2 23 2 24 4.8 25 4.8 26 4.8 On the Commission worksheet, in cell B4, troubleshoot and edit the function so that it retrieves the correct value of 5.00%. In cel B8, troubleshoot and edit the function so that it retrieves the correct value of $1,250 for a Manager 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.837 if they spent four days at the spa, cell A3 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). 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 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 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 (cell A3). 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 27 4.8 28 4.8 29 4.8