Microsoft Office Tutorials and References
In Depth Information
Tutorial C: Building a Decision Support System in Excel
Because you will present this model to your prospective lenders, you decide to use an Income and Cash
Flow Statements framework. You will input values for two possible states of the economy for 2012 and 2013:
R for a continuing recession and B for a
(recovery). Your sales in the recession were growing at 20%
per year. If the recession continues and you expand the business, you expect sales to continue growing at 30%
per year. However, if the economy recovers, some of your customers will switch to buying
expect sales growth for your thrift shop to be 15% above the previous year (only 5% growth plus 10% for the
business expansion). If you do not expand, your recession or boom growth percentages will only be 20% and
5%, respectively. To determine the cost of goods sold for purchasing your merchandise, which is currently 70%
of your sales, you will input values for two possible consumer price outlooks: H for high inflation (1.06
multiplied by the average cost of goods sold) and L for low inflation (1.02 multiplied by the cost of goods sold).
You currently own half the storefront and will need to borrow $100,000 to buy and renovate the other
half. The bank has indicated that, depending on your forecast, it may be willing to loan you the money for
your expansion at 5% interest during the current recession with a 10-year repayment compounded annually
). However, if the prime rate drops at the start of 2012 because of an economic turnaround (
bank can drop your interest rate to 4% with the same repayment terms.
As an entrepreneur, an item of immediate interest is your cash flow position with the additional burden
of a loan payment. After all, one of your main objectives is to make a profit (Net Income After Taxes). You
can use the DSS model to determine if it is more profitable not to expand the business.
Organization of the DSS Model
A well-organized spreadsheet will make the design of your DSS model easier. Your spreadsheet should have
the following sections:
Summary of Key Results
Calculations (with separate calculations for Expansion vs. No Expansion)
Income and Cash Flow Statements (with separate statements for Expansion vs. No Expansion)
Figures C-1 and C-2 illustrate the spreadsheet setup for the DSS model you want to build.
Tutorial skeleton 1