Chapter 7 Cumulative Exercise : Data Management

Open e07c1HotTubs and save it as e07c1HotTubs_LastFirst. Make sure the Orders worksheet is active.

Insert the WEEKDAY function in cell D2 to return the serial number representing the weekday that the first hot tub was ordered (cell E2).

Apply a custom number format dddd to display the actual weekday. Apply Left horizontal alignment. Copy the function to the range D3:D12.

Insert the DAYS function in cell G2 to calculate the number of days between the Date Ordered and Date Received for the first hot tub. Apply Center horizontal alignment. Copy the function to the range G3:G12.

Use Logical Functions

Next you will use logical functions. First, you will use the SWITCH function to switch the codes PD and CR for Paid in Full and Credit, respectively. Second, you will issue a store credit toward cleaning supplies if orders took over 45 or 70 days as a goodwill gesture. Finally, you will create a nested IF function that displays Send Gift Basket for customers who bought either the Luxury or Serenity model and paid in full.

Insert the SWITCH function in cell C2 to evaluate the first value in the Code column. Switch PD for Paid in Full and switch CR for Credit. Use text strings for the last four function arguments. Copy the function to the range C3:C12.

Insert the IFS function in cell I2. Evaluate if the Days for the first hot tub order was greater than 70. If so, return $100. If the Days for the hot tub was greater than 40, return $50. Otherwise, return $0. Use cell references to the criteria thresholds and store credits located in the range H15:I16. Use relative and mixed references correctly. Copy the function to the range I3:I12.

Insert an IF function with a nested AND function with a nested OR function in cell J2. The function should display Send Gift Basket if these conditions are met: (1) the model was either Luxury or Serenity and (2) the hot tab was Paid in Full. If the conditions are false, use an empty text string “”. Use text within the arguments and appropriate relative cell references. Copy the function to the range J3:J12.

Calculate Summary Statistics

Your assistant created a section for summary statistics below the main dataset. You will use conditional math and statistical functions to calculate the average number of days between the order and received dates and total quantities ordered by model. Then you will identify the highest, average, and lowest price for specific hot tub models.

Insert the AVERAGEIF function in cell B15 to calculate the average number of days for the Luxury model. Use mixed references to the range and criteria range. Use a relative reference to cell A15 that contains the model name. Do not type text as the criteria argument. Apply Number format with 0 decimal places and apply Center horizontal alignment. Copy the function to the range B16:B17 and preserve the existing border in cell B17.

Insert the COUNTIF function in cell C15 to calculate the quantity of each hot tub model. Use mixed references for the range and a relative reference to the first model in cell A15. Apply Center horizontal alignment. Copy the function to the range C16:C17 and preserve the existing border in cell C17.

Insert the MAXIFS function in cell E15 to calculate the highest-price Luxury model purchased on credit. Use mixed references to the range and criteria range arguments. Use a relative reference to the model name in cell A15. Use a mixed reference to the word Credit in cell E14.

Copy the function in cell E15 to cell E16 and preserve the existing border in cell E16. Edit the function in cell E16 to calculate the average price of the Serenity model purchased on credit. If you used correct mixed and relative references in the original function, the only edit is to change the function name to AVERAGEIFS.

Copy the function in cell E15 to cell E17 and preserve the existing border in cell E17. Edit the function in cell E17 to calculate the lowest price of the Bubbles model purchased on credit. If you used correct mixed and relative references in the original function, the only edit is to change the function name to MINIFS.

Insert a Map

Your assistant created a list to summarize the annual sales for the top five states. You will create a map to visually depict the data, change the size of the map, and display data labels.

Display the Map worksheet. Select the range A3:B8 and insert a map. Cut the map and paste it in cell D1.

Change the map title to Annual Hot Tub Sales and apply Blue, Accent 5, Darker 50% font color to the chart title.

Set 2.8″ height and 4.6″ width.

Display the Format Data Series task pane and show all map labels.

Loan Amortization

Customers often want to see a breakdown of loan payments. Your assistant started a loan amortization table for you. Now, you are ready to complete it. You will calculate the APR, display dates for a one-year loan, calculate monthly interest and principal repayment, and the remaining balance. Finally, you will calculate the cumulative interest and principal paid.

Display the Loan worksheet. Insert the RATE function in cell B3. Use the cell references to the price, term, and monthly payment. Multiply the result by 12 using a cell reference to calculate the APR. Make sure the function returns a positive value.

Insert the EDATE function in cell A8 to return the next month’s payment due date. Apply the Short Date number format, Align Right horizontal alignment, and indent once. Copy the function to the range A9:A18.

Insert the CUMIPMT function in cell E1 to calculate the cumulative interest. Use cell references to calculate the periodic rate, nper, present value (price) starting period, and ending period. Make sure the function returns a positive value.

Insert the CUMPRINC function in cell E2 to calculate the cumulative principal through the sixth month payment. Use cell references for all arguments. Make sure the function returns a positive value.

Insert the IPMT function in cell C7 to calculate the first month’s principal payment. Use relative and mixed cell references correctly. Copy the function to the range C8:C18.

Insert the PPMT function in cell D7 to calculate the first month’s interest. Use relative and mixed cell references correctly. Copy the function to the range D8:D18.

Insert a formula in cell E7 to calculate the remaining balance by subtracting the first principal repayment from the original price.

Insert a formula in cell E8 to calculate the remaining balance by subtracting the second principal repayment from the first month’s remaining balance. Copy the formula to the range E9:E18.

Format the range C7:E18 with Accounting Number Format.

Save and close the file. Exit Excel. Based on your instructor’s directions, submit: e07c1HotTubs_LastFirst