Chapter 5 – Module 10
Assessment 1: Convert Columns to Rows
- Open CRC.xlsx.
- Save the workbook with the name 5-CRC.
- Copy the range A4:F12, select cell A14, and paste the data so the columns become rows and vice versa.
- Delete rows 4 through 13 (the original rows of source data) from the worksheet.
- Adjust the merging and centering of the title rows across the top of the worksheet. Apply bold formatting to the range B4:I4 and AutoFit the width of each column in the worksheet.
- Copy the values in the Shipping column and paste them into the Total Cost column using an Add operation, so that the total cost now includes the shipping fee.
- Duplicate the validation rule for the values in the Compact column in the Mid-size and SUV columns. Hint: Copy the values in the Compact column and paste only the validation rule to the Mid-size and SUV columns.
- Save and then close 5-CRC
Assessment 2: Use Goal Seek
- Open NationalBdgt.xlsx.
- Save the workbook with the name 5-NationalBdgt
- Make cell D8 the active cell and open the Goal Seek dialog box.
- Find the projected increase for wages and benefits that will make the total cost of the new budget equal $855,000.
- Accept the solution that Goal Seek calculates.
- Save and then close 5-NationalBdgt
Assessment 3: Use Scenario Manager
- Open PreCdnTarget.xlsx.
- Save the workbook with the name 5-PreCdnTarget
- Create scenarios to save various percentage data sets for the four regions using the following information:
- Show the LowSales scenario and then print the worksheet.
- Create a scenario summary report that displays cell H 18 as the result cell.
- Save and then close 5-PreCdnTarget.
a. A scenario named OriginalTarget that stores the current values in the range K4:K7.
b. A scenario named LowSales with the following values:
c. A scenario named HighSales with the following values:
Visual Benchmark: Find the Base Hourly Rate for Drum Lessons
- Open Lessons.xlsx.
- Save the workbook with the name 5-Lessons
- The current worksheet is shown in Figure WB-5.l. The hourly rates in the range B4:B12 are linked to the cell named BaseRate, which is cell B15. For intermediate and advanced lessons, $4 and $8, respectively, is added to the hourly base rate.
- The drum teacher wants to earn $4,770 per month from teaching lessons (instead of the current total of $4,298). Use the Goal Seek feature to change the base hourly rate to the value required to reach the drum teacher’s target.
Save, print, and then close