Vidya
3 views | +0 today
Follow
 
Rescooped by Dave Matthews from Homework
onto Vidya
Scoop.it!

YO16_XL_CH03_GRADER_PC_HW - Wedding 1.3 (completed solution)

YO16_XL_CH03_GRADER_PC_HW - Wedding 1.3 (completed solution) | Vidya | Scoop.it

Via vickminor
more...
vickminor's curator insight, April 3, 2017 5:19 AM

YO16_XL_CH03_GRADER_PC_HW - Wedding 1.3

 

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.

 

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Start Excel. Open the file namede02ch03_grader_pc_Wedding.xlsx. Save the file with the name e02ch03_grader_pc_Wedding_LastFirst, replacingLastFirst with your last and first name.


0.000

2

On the GiftBaskets worksheet, in cell F9, use AutoSum to calculate the total items in basket 1. AutoFill the function down to cell F11.

3.000

3

In cell B15, enter a formula that will calculate the price of a lavender candle after the bulk discount in cell E3 is applied. AutoFill the formula over to E15.

5.000

4

In cell B18, enter a formula that will calculate the total price for the number of lavender candles in basket 1 using the price after bulk discount. AutoFill the formula over to E18 and then down to E20.

4.000

5

In cell E14, change the price of Soothing Bath Salts to $11.99.

1.000

6

Create a named range of BasketSubtotals for the range C23:C25.

2.000

7

Edit the BasketsRequested named range so that it refers to cells B23:B25.

2.000

8

In cell F24, use the SUM function to add up all the values in the BasketSubtotals named range.

3.000

9

Use the range A18:E20 to create named ranges using the values in the left column as the names.

2.000

10

Apply the named ranges just created to the formulas in cells F18:F20.

2.000

11

On the WeddingSummary worksheet, in cell C9 use the INT function to calculate the number of whole days the wedding party spent at the resort. Decrease the number of decimal places to 0. AutoFill the function down to C39.

2.000

12

In cell E9, use the appropriate function to calculate the absolute value of the returned merchandise. AutoFill the function down to E39.

2.000

13

In cell C2, edit the function to round the result to the nearest penny.

3.000

14

In cell C1, use the MODE.SNGL function to determine the most frequent length of stay using whole days.

5.000

15

In cell C3, use a function to calculate the median amount spent at the spa by the wedding parties.

5.000

16

In cell C4, use a function that will calculate the number of wedding parties served by counting the wedding party names.

5.000

17

In cell C5, use a function to calculate the number of spa members scheduling weddings by counting the numbers in column G.

5.000

18

On the SpaServices worksheet, in cell B1, use a function to return the current date.

3.000

19

In cell C5, use the function =DATEDIF(B5,$B$1,"Y") to calculate the age of the guest, using her date of birth and todays date in cell B1. AutoFill the function down to C22.

3.000

20

In cell D5 use the TRIM, LEFT, and FIND text functions to extract the guests first name from cell A5. AutoFill the function down to D22.

10.000

21

In cell G5, use Flash Fill to separate the city from column F.

3.000

22

In cell H5, use Flash Fill to separate the state from column F.

3.000

23

On the WeddingFinancing worksheet, in cell B7, use a VLOOKUP function to retrieve appropriate percentage discount based on the total cost of the event in cell B3.

6.000

24

In cell B8, use a formula to calculate the amount of the discount by multiplying the total cost of the event by the percentage discount.

2.000

25

In cell B10, use the PMT function to calculate the equal monthly payment amount necessary to pay off the amount borrowed in cell B9, at the end of the term in cell B6, using the APR in cell B5. Be sure the result of the PMT function is positive.

5.000

26

In cell C10, use an appropriate function to return Sufficient Credit Score if the credit score is greater than or equal to 650and Insufficient Credit Score if not.

5.000

27

In cell A18, use the appropriate functions to return 500 as the all-inclusive discount if the values in cells A13 and A15 are bothYes and return 0 if not. Change the value in cell A13 to Yes.

5.000

28

On the Commission worksheet, in cell B4, troubleshoot and edit the function so that it retrieves the correct value of 5.00%.

2.000

29

In cell B8, troubleshoot and edit the function so that it retrieves the correct value of $1,250 for a Manager.

2.000

30

Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

0.000

 

Total Points

100.000

Your new post is loading...
Your new post is loading...
Rescooped by Dave Matthews from Homework
Scoop.it!

YO16_XL_CH03_GRADER_PC_HW - Wedding 1.3 (completed solution)

YO16_XL_CH03_GRADER_PC_HW - Wedding 1.3 (completed solution) | Vidya | Scoop.it

Via vickminor
more...
vickminor's curator insight, April 3, 2017 5:19 AM

YO16_XL_CH03_GRADER_PC_HW - Wedding 1.3

 

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.

 

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Start Excel. Open the file namede02ch03_grader_pc_Wedding.xlsx. Save the file with the name e02ch03_grader_pc_Wedding_LastFirst, replacingLastFirst with your last and first name.


0.000

2

On the GiftBaskets worksheet, in cell F9, use AutoSum to calculate the total items in basket 1. AutoFill the function down to cell F11.

3.000

3

In cell B15, enter a formula that will calculate the price of a lavender candle after the bulk discount in cell E3 is applied. AutoFill the formula over to E15.

5.000

4

In cell B18, enter a formula that will calculate the total price for the number of lavender candles in basket 1 using the price after bulk discount. AutoFill the formula over to E18 and then down to E20.

4.000

5

In cell E14, change the price of Soothing Bath Salts to $11.99.

1.000

6

Create a named range of BasketSubtotals for the range C23:C25.

2.000

7

Edit the BasketsRequested named range so that it refers to cells B23:B25.

2.000

8

In cell F24, use the SUM function to add up all the values in the BasketSubtotals named range.

3.000

9

Use the range A18:E20 to create named ranges using the values in the left column as the names.

2.000

10

Apply the named ranges just created to the formulas in cells F18:F20.

2.000

11

On the WeddingSummary worksheet, in cell C9 use the INT function to calculate the number of whole days the wedding party spent at the resort. Decrease the number of decimal places to 0. AutoFill the function down to C39.

2.000

12

In cell E9, use the appropriate function to calculate the absolute value of the returned merchandise. AutoFill the function down to E39.

2.000

13

In cell C2, edit the function to round the result to the nearest penny.

3.000

14

In cell C1, use the MODE.SNGL function to determine the most frequent length of stay using whole days.

5.000

15

In cell C3, use a function to calculate the median amount spent at the spa by the wedding parties.

5.000

16

In cell C4, use a function that will calculate the number of wedding parties served by counting the wedding party names.

5.000

17

In cell C5, use a function to calculate the number of spa members scheduling weddings by counting the numbers in column G.

5.000

18

On the SpaServices worksheet, in cell B1, use a function to return the current date.

3.000

19

In cell C5, use the function =DATEDIF(B5,$B$1,"Y") to calculate the age of the guest, using her date of birth and todays date in cell B1. AutoFill the function down to C22.

3.000

20

In cell D5 use the TRIM, LEFT, and FIND text functions to extract the guests first name from cell A5. AutoFill the function down to D22.

10.000

21

In cell G5, use Flash Fill to separate the city from column F.

3.000

22

In cell H5, use Flash Fill to separate the state from column F.

3.000

23

On the WeddingFinancing worksheet, in cell B7, use a VLOOKUP function to retrieve appropriate percentage discount based on the total cost of the event in cell B3.

6.000

24

In cell B8, use a formula to calculate the amount of the discount by multiplying the total cost of the event by the percentage discount.

2.000

25

In cell B10, use the PMT function to calculate the equal monthly payment amount necessary to pay off the amount borrowed in cell B9, at the end of the term in cell B6, using the APR in cell B5. Be sure the result of the PMT function is positive.

5.000

26

In cell C10, use an appropriate function to return Sufficient Credit Score if the credit score is greater than or equal to 650and Insufficient Credit Score if not.

5.000

27

In cell A18, use the appropriate functions to return 500 as the all-inclusive discount if the values in cells A13 and A15 are bothYes and return 0 if not. Change the value in cell A13 to Yes.

5.000

28

On the Commission worksheet, in cell B4, troubleshoot and edit the function so that it retrieves the correct value of 5.00%.

2.000

29

In cell B8, troubleshoot and edit the function so that it retrieves the correct value of $1,250 for a Manager.

2.000

30

Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

0.000

 

Total Points

100.000