Microsoft Office Tutorials and References
In Depth Information
Tutorial C: Building a Decision Support System in Excel
￿
The Summary of Key Results section should be just that
make good business decisions. Key results frequently include net income before taxes (profit)
and end-of-year cash on hand (how much cash your business has). However, if you are creating
a DSS model on alternative capital projects, your key results can also include cost savings, net
present value of a project, or rate of return for an investment.
￿
The Constants section is for known values you need to perform other calculations. You use a
Constants section rather than just including those values in formulas so that you can input new
values if they change, such as tax rates. It makes your DSS model more flexible.
AT THE KEYBOARD
Enter the Excel skeleton shown in Figures C-1 and C-2.
NOTE
When you see NA (Not Applicable) in a cell, do not enter any values or formulas in the cell. The cells that contain values in the
2011 column are used by other cells for calculations. In this example, you are mainly interested in what happens in 2012 and
2013. The rest of the cells are “Not Applicable.”
Filling in the
Formulas
The next step in building a spreadsheet is to fill in the
Easy
formulas. To begin, format all the cells that will
contain monetary values as Currency with zero decimal places:
easy
￿
Constants
C5
￿
Summary of Key Results
C12 to C15, D12 to D15
￿
Calculations (Expansion)
C18, C19, D18, D19
￿
Calculations (No Expansion)
C24, C25, D24, D25
￿
Income and Cash Flow Statements (Expansion)
B36, C29 to C36, D29 to D36
￿
Income and Cash Flow Statements (No Expansion)
B45, C39 to C45, D39 to D45
NOTE
With the insertion point at cell C12 (where the \$0 appears), note the editing window—the white space at the top of the
spreadsheet to the right of the fx symbol. The cell’s contents, whether it is a formula or value, should appear in the editing window. In
this case, the window shows =C35.
The Summary of Key Results section (see Figure C-3) will contain the values you calculate in the Income and
Cash Flow Statements sections. To copy the cell contents for this section, move your mouse cursor to cell C12, click
the cell, type =C35, and press Enter. If you formatted your money cells properly, a \$0 should appear in cell C12.
FIGURE C-3
Value from cell C35 (Net Income after Taxes) copied to cell C12

Search JabSto ::

Custom Search