Microsoft Office Tutorials and References
In Depth Information
In the Lab
copied to cells D4 and D5. The current IF functions in cells D3, D4, and D5 are incorrect. Edit
and correct the IF function in cell D3. Copy the corrected formula in cell D3 to cells D4 and D5.
After completing the copy, click the Auto Fill Options button arrow that displays below and to the
right of cell D5 and choose Fill Without Formatting.
3. The Manufacturing Costs in cell E3 is computed using the formula =B11*B3 (Manu. Costs % x
Sales). The formula in cell E3 was entered and copied to cells E4 and E5. Although the result in
cell E3 is correct, the results in cells E4 and E5 are incorrect. Edit and correct the formula in cell
E3 by changing cell B11 to an absolute cell reference. Copy the corrected formula in cell E3 to
cells E4 and E5. After completing the copy, click the Auto Fill Options button arrow that displays
below and to the right of cell E5 and choose Fill Without Formatting.
4. Change the design of the worksheet by moving the Assumptions table in the range A8:B11 to
the range A1:B4 as shown in Figure 3–84b. To complete the move, insert ﬁ ve rows above row 1
and then drag the Assumptions table to the range A1:B4. Use Figure 3–84b to verify that Excel
automatically adjusted the cell references based on the move. Use the Undo button and Redo
button on the Quick Access Toolbar to move the Assumptions table back and forth while the
results of the formulas remain the same.
5. Change the document properties, as speciﬁ ed by your instructor. Change the worksheet header
with your name, course number, and other information requested by your instructor. Save the
workbook using the ﬁ le name, Make It Right 3-1 e-MusicPro.com Annual Projected Net Income
Complete, and submit the revised workbook as requested by your instructor.
In the Lab
Create a workbook using the guidelines, concepts, and skills presented in this chapter. Labs
are listed in order of increasing difﬁ culty.
Lab 1: Eight-Year Financial Projection
Problem: Your supervisor in the Finance department at Salioto Auto Parts has asked you to create
a worksheet that will project the annual gross margin, expenses, total expenses, operating income,
income taxes, and net income for the next ten years based on the assumptions in Table 3–9. The desired
worksheet is shown in Figure 3–85. In Part 1 you will create the worksheet. In Part 2 you will create
a chart to present the data, shown in Figure 3–86. In part 3 you will use Goal Seek to analyze three
different sales scenarios.
Table 3–9 Salioto Auto Parts Financial Projection Assumptions
Units Sold in Prior Year
Annual Sales Growth
Annual Price Decrease