Bootsies for Tootsies is a new leisure store that will be taking over $5,000$ square feet of the

Hudson$$s Bay department store at Aberdeen Mall. Bootsies will install $$300,000$ of fixed assets

$($shelving$,$ displays, counters, etc.$)$ in the space. Those assets will depreciate using a straight$-$line

method over the next six years. The corporate tax rate is $33\%.$ The inflation rate is estimated to

be $2\%.$ The cost of merchandise is $30\%$ of sales. All three rates are expected to remain the same

for the foreseeable future.

$$ Labour cost is $$220,000$ in the first year; they$$ll grow with the inflation rate after that.

$$ Other expenses are $$320,000$ in the first year and grow with inflation.

$$ Rent is currently $25$ $$/$ft$2$ it too will increase at the rate of inflation over time.

First year sales are forecast to be $$832,099.$ Sales will grow $20\%$ in the $2$nd year, $12\%$ in the $3$rd

year, $9\%$ in the $4$th year, then flatten out to $5\%$ for years $5$ & $6.$ We will be using a discounted

case flow $($DCF$)$ approach for this model $$ the discount rate will be $10\%.$

$$ Note: per FAST Modelling Standard $4\mathrm{.}01-02$ Do not use the NPV function $$ ever! I$$ll give

you the DCF formulas below.

All the stuff above are the input parameters.

Your model will run for six years. Here are the key ideas...

Sales Revenue $$ Year$1($given$),$ Year$2=20\%$ growth over Year$1,$ etc.

Cost of Merchandise $\%$ of Sales X Sales $($easy stuff, hey?! $)$

Operating Expenses

Labour Cost $$ Year$1($given$),$ Year$2$ higher due to inflation...

Other Expenses $$ Year$1($given$),$ Year$2$ higher due to inflation...

Rent $$ Year$1($given$),$ Year$2$ higher due to inflation...

Net Operating Income $$ Sales minus Cost of Merchandise minus Operating Expenses.

Depreciation Expense $$ Fixed Assets $-$: Depreciation Period.

Net Income Before Tax $$ Net Operating Income less Depreciation.

Income Tax $$ Net Income Before Tax times Tax Rate. Note: if there is a loss, taxes $=$ zero.

Net Income After Tax $$ surely you can figure this out on your own $$

Annual Cash Flow $$ you$$ll need to add Depreciation back in to Net Income After Tax to get the

Annual Cash Flow.

Discounted Cash Flow: $!="$#$!$

$(\%$&$\text{'})!$ the discounted cash flow in year n $($n$=1,2,...,6)$ is the

annual cash flow in year n divided by $(1+$ r$)$ to the nth power where r is the discount rate...

Cumulative Discounted Cash Flow $$ pretty much what it sounds like: $=!!$

$\%$

$$ If you build your model correctly, you should end up with a Cumulative Discounted Cash

Flow in year $6$ of $$250,000.$Because Boosties for Tootsies is going into a large department store space, there is a lot of

flexibility with respect to the store$$s footprint. The owners are also considering a $$More Merch$$

strategy whereby they would use the original $5,000$ ft$2$ footprint, but diversify their product line

from just footwear and include accessories $$ purses, bags, etc. Use Excel$$s Scenario Manager

to create three scenarios:

$$ Small Store $4,500$ ft$2$

$.250$K$ investment in fixed assets. $200$K$ labour cost. $700$K$ in

sales in first year.

$$ Large Store $5,700$ ft$2$

$.360$K$ investment in fixed assets. $260$K$ labour cost. $900$K$ in

sales in first year.

$$ More Merch $250$K$ in labour cost. Other expenses: $325$K$$.$ First year sales $850$K$$.$

Scenarios should consider topline Sales Revenue, Net Income Before Tax, and Cumulative

Discounted Cash Flow in year six.

$$ Save your work!

Add a section to the right or down below your model to explore the impact of cost of

merchandise changes and inflation rate assumptions in a $2-$way data table. What if$...$ inflation

runs from $0\mathrm{.}5\%$ to $3\mathrm{.}5\%?$ What if$...$ cost of merchandise runs from $28\%$ of sales to $36\%$ of sales?

Use the Annual Cash Flow in year six as your parameter of interest.