Microsoft Office Tutorials and References
In Depth Information
Tutorial C: Building a Decision Support System in Excel
Tutorial skeleton 2
Each spreadsheet section is discussed in detail next.
The Constants Section
This section holds values that are needed for the spreadsheet calculations. These values are usually given to
you, and generally do not change for the exercise. However, you can change these values later if necessary;
for example, you might need to borrow more or less money for your business expansion (cell C5). For this
tutorial, the constants are the Tax Rate and the Loan Amount.
The Inputs Section
The Inputs section in Figure C-1 provides a place to designate the two possible economic outlooks and the
two possible inflation outlooks. If you wanted to make these outlooks change by business year, you could
leave blanks under both business years. However, as you will see later when you use Scenario Manager, this
approach would greatly increase the complexity of interpreting the results. For simplicity
s sake, assume that
the same outlooks will apply to both years 2012 and 2013.
The Summary of Key Results Section
This section summarizes the Year 2 and 3 Net Income after Taxes (profit) and the End-of-year Cash on Hand
both for expanding the business and for not expanding. These cells are copied from the Income and Cash
Flow Statements section at the bottom of the sheet. Summary sections are frequently placed near the top of
a spreadsheet to allow managers to see a quick
summary without having to scroll down the
spreadsheet to see the final result. Summary sections can also make it easier to select cells for charting.
The Calculations Sections (Expansion and No Expansion)
The following areas are used to compute the following necessary results:
The Total Sales Dollars, which is a function of the Year 2011 value and the Economic Outlook
The Cost of Goods Sold, which is the Total Sales Dollars multiplied by the Cost of Goods Sold
(as a percent of Sales)
The Cost of Goods Sold (as a percent of Sales), which is a function of the Year 2011 value and
the Inflation Outlook input
In addition, the Calculations section for the expansion includes the interest rate, which is also a
function of the Economic Outlook input. This interest rate will be used to determine the
Business Loan Payment in the Income and Cash Flow Statements section.
You could make these formulas part of the Income and Cash Flow Statements section. However, it makes
more sense to use the approach shown here because it makes the formulas in the Income and Cash Flow