Question

Data: Opening Grant’s Emporium II

The capital investment required for opening a new store is $1,500,000, covering shop-fitting and other equipment such as cash registers etc. Additional working capital of $75,000 is also needed. The fixed costs of running a new store are $90,000 in the first year while variable costs, including labour costs, are $120,000. Both fixed and variable costs are expected to grow in line with inflation at 2 percent p.a. Forecast sales are $780,000 in the first year but can be expected to grow by 5 percent p.a. Advertising is important to this type of business and will cost $60,000 per year. The capital investment is to be depreciated straight-line to zero. W.T. hopes to keep the store running for 10 years. The discount rate that your team believes should be applied to the cash flows is 12 percent. The taxation rate is 27.5 percent (because this is a small-medium business in Australia). All working capital is returned at the end of the project. You intend to tell W.T. the NPV and IRR for this project idea and advise him on whether or not the idea will create market value for him and his family who own and operate the stores.

Your manager’s guidance: In addition to presenting the NPV and IRR (and associated advice), your manager, having worked with W.T. before, knows that he likes to scope out as many dimensions of a potential business decision as possible before committing. No doubt, W.T. will be interested in the sensitivity of the NPV and IRR to changes in forecast sales and the forecast growth rate. He might also be interested in knowing whether the project is viable if costs are greater (or grow faster) than expected or if additional working capital is required at different points. Being a bit of a finance buff, W.T. might also want to know more about the discount rate and how changes in it affect the viability of the project. You will need to make sure your spreadsheet analysis can cope with these adjustments so that you can quickly work out your answers during the meeting.

Answer 1

Operating cash flow (FCF) each year = income after tax + depreciation - investment in working capital

In year 10, the entire working capital investment is recovered, and hence the investment in working capital is negative

NPV and IRR are calculated using NPV and IRR functions in Excel

NPV is $1,353,171

IRR is 28.37%

H 1 First year sales 2 Sales growth rate 3 Variable costs 4 B $ 780,000 5% $ 120,000 8 9 10 $1,500,000 6 Initial Investment 7 Cost of equipment 8 9 OCF 10 Revenues 11 - Variable Costs 12 - Fixed Costs 13 - Advertising 14 - Depreciation 15 Income before tax 16 - Taxes 17 Income after tax 18 + Depreciation 19 Investment in working capital 20 OCF $ 780,000 $819,000 $859,950 $902,948 $948,095 $995,500 $120,000 $122,400 $124,848 $127,345 $129,892 $132,490 $90,000 $91,800 $93,636 $95,509 $97,419 $99,367 $60,000 $60,000 $60,000 $60,000 $60,000 $60,000 $150,000 $150,000 $150,000 $150,000 $150,000 $150,000 $360,000 $394,800 $431,466 $470,094 $510,784 $553,643 $99,000 $108,570 $118,653 $129,276 $140,466 $152,252 $261,000 $286,230 $312,813 $340,818 $370,318 $401,391 $150,000 $150,000 $150,000 $150,000 $150,000 $150,000 $75,000 ($75,000) $411,000 $436,230 $462,813 $490,818 $520,318 $551,391 $1,045,275 $135,139 $101,355 $60,000 $150,000 $598,780 $164,665 $434,116 $150,000 $1,097,538 $137,842 $103,382 $60,000 $150,000 $646,314 $177,736 $468,578 $150,000 $1,152,415 $140,599 $105,449 $60,000 $150,000 $696,367 $191,501 $504,866 $150,000 $1,210,036 $143,411 $107,558 $60,000 $150,000 $749,067 $205,993 $543,073 $150,000 ($75,000) $768,073 $584,116 $618,578 $654,866 21 22 NPV Analysis 23 Project Cash Flows ($1,575,000) $411,000 $436,230 $462,813 $490,818 $520,318 $551,391 $584,116 $618,578 $654,866 $768,073 25 NPV 26 IRR $1,353,171 28.37%