Microsoft Office Tutorials and References
In Depth Information
Project — Financial Projection Worksheet with What-If Analysis and Chart EX 165
As you read this chapter, you will learn how to create the worksheet shown in Figure 3–1
by performing these general tasks:
• Create a series of month names
• Use absolute cell references in a formula
• Use the IF function to perform a logical test
• Use the Format Painter button to format cells
• Create a 3-D Pie chart on a separate chart sheet
• Answer what-if questions
• Manipulate large worksheets
General Project Decisions
While creating an Excel worksheet, you need to make several decisions that will determine
the appearance and characteristics of the fi nished worksheet. As you create the worksheet
required to meet the requirements shown in Figure 3–2, you should follow these general
1. Plan the layout of the worksheet. The requirements state that six months are necessary
in the worksheet. It is therefore sensible to place the months across columns so that the
fi nancial headings can be placed in rows. The what-if assumptions should not clutter the
worksheet, but they should be placed in an easily located portion of the worksheet.
2. Determine the necessary formulas and functions needed. Except for the monthly sales
numbers, the remaining numbers in the main portion of the worksheet are calculated based
on the numbers in the what-if portion of the worksheet. The formulas are stated in the
requirements document (Figure 3–2). The Bonus expense is included only if a certain condition
is met. A function can check for the condition and include the bonus when necessary.
3. Identify how to format various elements of the worksheet. Sales and Expenses are two
distinct categories of fi nancial data and should be separated visually. Gross Margin and
Total Expenses should stand out because they are subtotals. The Operating Income is the
key piece of information being calculated in the worksheet and, therefore, should be
formatted in such a manner as to draw the reader’s attention. The what-if assumptions
should be formatted in a manner which indicates that they are separate from the main
area of the worksheet.
4. Specify how the chart should convey necessary information. The requirements document
indicates that the chart should be a 3-D Pie chart and emphasize the month with the
greatest operating income. A 3-D Pie chart is a good way to compare visually a small set
of numbers. The month, which is emphasized, also should appear closer to the reader in
order to draw the reader’s attention.
5. Perform what-if analysis and goal seeking using the best techniques. What-if analysis allows
you quickly to answer questions regarding various predictions. In Campus Clothiers
Semiannual Financial Projection worksheet, the only cells that you should change when
performing what-if analysis are those in the what-if portion of the worksheet. All other
values in the worksheet, except for the projected sales, are calculated. Goal seeking allows
you automatically to modify values in the what-if area of the worksheet based on a goal
that you have for another cell in the worksheet.
Search JabSto ::

Custom Search