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

“

boom

”

so you

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

(

“

new,

”

“

R

”

). However, if the prime rate drops at the start of 2012 because of an economic turnaround (

“

B

”

), the

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:

Constants

Inputs

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.

FIGURE C-1

Tutorial skeleton 1