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
key results. These outputs help you
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.
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
The next step in building a spreadsheet is to fill in the
formulas. To begin, format all the cells that will
contain monetary values as Currency with zero decimal places:
Summary of Key Results
C12 to C15, D12 to D15
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
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.
Value from cell C35 (Net Income after Taxes) copied to cell C12