Building a Decision Support System In Excel

Tutorial Exercise--Collegetown Thrift Shop
Constants	2015	2016	2017
Tax Rate	NA	33%	35%
Loan Amount for Store Expansion	NA	$100,000	NA
Inputs	2015	2016	2017
Economic Outlook (R=Recession, B=Boom)	NA	R	NA
Inflation Outlook (H=High, L=Low)	NA	H	NA
Calculations (Expansion)	2015	2016	2017
Total Sales Dollars	$350,000	$455,000	$591,500
Cost of Goods Sold	$245,000	$337,610	$465,227
Cost of Goods Sold (as a percent of Sales)	70%	74%	79%
Interest Rate for Business Loan	5%	NA	NA
Calculations (No Expansion)	2015	2016	2017
Total Sales Dollars	$350,000	$420,000	$504,000
Cost of Goods Sold	$245,000	$311,640	$396,406
Cost of Goods Sold (as a percent of Sales)	70%	74%	79%
Income and Cash Flow Statements (Expansion)	2015	2016	2017
Beginning-of-year Cash on Hand	NA	$15,000	$84,974
Sales (Revenue)	NA	$455,000	$591,500
Cost of Goods Sold	NA	$337,610	$465,227
Business Loan Payment	NA	($12,950)	($12,950)
Income before Taxes	NA	$104,440	$113,323
Income Tax Expense	NA	$34,465	$39,663
Net Income after Taxes	NA	$69,974	$73,660
End-of-year Cash on Hand	$15,000	$84,974	$158,634
Income and Cash Flow Statements (No Expansion)	2015	2016	2017
Beginning-of-year Cash on Hand	NA	$15,000	$87,601
Sales (Revenue)	NA	$420,000	$504,000
Cost of Goods Sold	NA	$311,640	$396,406
Income before Taxes	NA	$108,360	$107,594
Income Tax Expense	NA	$35,759	$37,658
Net Income after Taxes	NA	$72,601	$69,936
End-of-year Cash on Hand	$15,000	$87,601	$157,537
Summary of Key Results	2015	2016	2017
Net Income after Taxes (Expansion)	NA	$69,974	$73,660
End-of-year Cash on Hand (Expansion)	NA	$84,974	$158,634
Net Income after Taxes (No Expansion)	NA	$72,601	$69,936
End-of-year Cash on Hand (No Expansion)	NA	$87,601	$157,537

Case 1: Collegetown Thrift Shop

You are the owner of a thrift shop that resells clothing in a university town. Your business is unusual in that sales actually increase during an economic recession. Your cost of obtaining used items follows the consumer price index. The business has been very good due to continuing recession. You are thinking of expanding your business, but you have to apply for a business loan to finance the purchase. The bank requires a projection of your profit and cash flows for the next two years (2016 and 2017) before it will loan the money to you to expand. You decide the forecast should be based on 4 factors: 1. Your 2015 sales dollars, 2.Your cost of goods sold per sales dollar, 3. Estimates of underlying economy, 4. Business payment loan amount and interest rate.

Because you will present this model to your prospective lenders, you decide to use and Income and Cash flow statement framework. You will input two possible states of the economy for 2016 and 2017: R for recession and B for Boom. If you expand your business, you expect the sales growth to be 30% with recession. However, if the economy recovers, you expect 15% sales growth. If you dont expand your business, you expect 20% sales growth with recession and 5% sales growth with boom.

To determine cost of goods, which is currently 70% of your sales, you will input values for possible two possible consumer price outlooks: H for high inflation (1.06 multiplies 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 of the storefront and will need to borrow $100,000 to buy and renovate the other half. The bank indicated they might be willing to lend you money at 5% interest during recession with a 10-year repayment compounded annually. If the economy recovers (boom), the bank can drop your interest rate to 4% with the same repayment terms.

Determine whether it is profitable to expand your business or not?