Microsoft Office Tutorials and References
In Depth Information
Tutorial C: Building a Decision Support System in Excel
Statements less complicated. In addition, when you create other DSS models that include unit costing and
pricing calculations, you can enter the formulas in this section to facilitate managerial accounting cost analysis.
The Income and Cash Flow Statements Sections (Expansion and No Expansion)
These sections are the financial or accounting
of the spreadsheet. They contain the following values:
Beginning-of-year Cash on Hand, which equals the prior year
s End-of-year Cash on Hand.
Sales (Revenue), which in this tutorial is simply the results of the Total Sales Dollars copied
from the Calculations section.
Cost of Goods Sold, which also is copied from the Calculations section.
Business Loan Payment, which is calculated using the PMT (Payment) function and the inputs
for loan amount and interest rate from the Constants and Calculations sections. Note that only
the Income and Cash Flow Statement for Expansion includes a value for Business Loan Payment.
If you do not expand, you do not need to borrow the money.
Income before Taxes, which is Sales minus the Cost of Goods Sold; for the expansion scenarios,
you also subtract the Business Loan Payment.
Income Tax Expense, which is zero when there is no income or negative income; otherwise, this
value is the Income before Taxes multiplied by the Tax Rate from the Constants section.
Net Income after Taxes, which is Income before Taxes minus Income Tax Expense.
End-of-year Cash on Hand, which is Beginning-of-year Cash on Hand plus Net Income after Taxes.
Note that this Income and Cash Flow Statement is greatly simplified. It does not address the issues of
changes in Inventories, Accounts Payable, and Accounts Receivable, nor any period expenses such as Selling
and General Administrative expenses, utilities, salaries, real estate taxes, insurance, or depreciation.
Construction of the Spreadsheet Model
Next, you will work through three steps to build the spreadsheet model:
1. Make a skeleton or
of the spreadsheet. Save it with a name you can easily recognize, such
as TUTC.xlsx or Tutorial C YourName.xlsx. When submitting electronic work to an instructor or
supervisor, it is always a good idea to include your last name and first initial in the filename.
2. Fill in the
3. Then enter the
Making a Skeleton or
The first step is to set up the skeleton worksheet. The skeleton should have headings, text labels, and
constants. Do not enter any formulas yet.
Before you start entering data, you should first try to visualize a sensible structure for your worksheet. In
Figures C-1 and C-2, the seven sections are arranged vertically down the page; the item descriptions are in
the first column (A), and the time periods (years) are in the next three columns (B, C, and D). This is a
widely accepted business practice, and is commonly called a
It is used to visually
compare financial data side by side through successive time periods.
Because your key results depend on the Income and Cash Flow Statements, you usually set up that
section first, and then work upward to the top of the sheet. In other words, you set up the Income and Cash
Flow Statements section, then the Calculations section, and then the Summary of Key Results, Inputs, and
Constants sections. Some might argue that the Income and Cash Flow Statements should be at the top of
the sheet, but when you want to change values in the Constants or Inputs section or examine the Summary
of Key Results, it does not make sense to have to scroll to the bottom of the worksheet. When you run the
model, you do not enter anything in the Income and Cash Flow Statements
they are all calculations. So, it
makes sense to put them last.
Here are some other general guidelines for designing effective DSS spreadsheets:
Decide which items belong in the Calculations section. A good rule of thumb is that if your items
have formulas but do not belong in the Income and Cash Flow Statements, put them in the
Calculations section. Good examples are intermediate calculations such as unit volumes, costs
and prices, markups, or changing interest rates.