# save various percentage data sets B u s i n e s s F i n a n c e

save various percentage data sets B u s i n e s s F i n a n c e

CMR 282

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:

East 0.20

West 0.32

Ontario 0.48

Quebec 0.37

c. A scenario named HighSales with the following values:

East 0.36

West 0.58

Ontario 0.77

Quebec 0.63

# 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 5-Lessons