Question

You may build some tables for this assignment in Excel and please show all details. If you use excel, please just copy and paste.

Your goal is to figure out whether starting a microbrewery in Nova Scotia is a good idea or not. You need to perform net present value analysis. In addition, you need to estimate payback period, discounted payback, internal rate of return and profitability index. If you cannot estimate a certain parameter, indicate why. The inputs for your analysis are as follows:

you will run the business for ten years, and then you close the shop.

net sales forecasts are as follows:   

year one year two year three

$200,000 $1,000,000 $2,000,000

Starting year four, your sales will grow at a rate of three percent until year ten, when you close the business. You could grow sales faster if you expanded into other provinces, but provincial governments in Canada impose regulatory hurdles on beer imports from other provinces.[1]

costs of goods sold are sixty five percent of sales.

selling, general and administrative expenses are 25 percent of sales.

initial investment in equipment equals $600,000. Unless you choose to use a CCA formula, you will need to build a CCA schedule for ten years of operations. For simplicity, assume that there are no more capital expenditures and that at the end of year ten, equipment is sold at remaining book value (UCC). CCA rate equals 30 percent.

Your federal tax rate is 11 percent[2]. You pay no provincial tax in the first three years of operations[3]. After that, you start paying provincial tax at a rate of 4.5 percent [4]. For simplicity, assume that losses accumulated in the first years cannot be applied to reduce taxable income in later years, ie there are no tax loss carry forwards.

Equipment purchase for a microbrewery costs $600,000 (see extract from The Wall Street Journal article on the next page). Half of that amount is funded with a loan that carries interest rate of 7 percent.

starting a brewery requires immediate working capital outlay of $10,000. Net working capital is maintained at 5 percent of sales throughout the life of the project.

cost of capital equals 20 percent, slightly below returns required by late stage venture capital firms.

To facilitate your task, I built income statement for year one (see attachment 2). I suggest you build the rest of the income statement and CCA schedule in Excel, and copy/paste them into your deliverable. If you choose to use the CCA formula, you need to eliminate depreciation from the income statement.

Attachment 1 Pro forma income statement for the first year of operations.

net sales                                   $200,000

cost of goods sold                    $130,000

gross profit $70,000

overheads $50,000

depreciation $90,000

operating profit (EBIT)           -$70,000

interest $21,000

pre-tax profit -$91,000

tax $0 (no tax is paid on negative earnings)                  

net income -$91,000

Note that for net present value analysis, operating cash flow should be estimated based on after-tax operating profit that excludes interest tax shield. Use equation 11.2 from recommended text.

depreciation schedule year one

book value starting balance $600,000

depreciation $90,000

book value ending balance $510,000

Answer 1

Alright first off. thanks for computing the CCA schedule & the income statement for the first year. I'd still do it all over again so that you can see all the details properly(PS - the depreciation you've calculated is at 15% while the problem prescribes 30%, so I'm gonna use 30% instead).

The CCA Schedule is presented below:

Year	1	2	3	4	5	6	7	8	9	10
Opening Value	600,000.00	420,000.00	294,000.00	205,800.00	144,060.00	100,842.00	70,589.40	49,412.58	34,588.81	24,212.16
- Depreciation@ 30%	(180,000.00)	(126,000.00)	(88,200.00)	(61,740.00)	(43,218.00)	(30,252.60)	(21,176.82)	(14,823.77)	(10,376.64)	(7,263.65)
Closing Value	420,000.00	294,000.00	205,800.00	144,060.00	100,842.00	70,589.40	49,412.58	34,588.81	24,212.16	16,948.51

Now let's move towards the Cash flows from the business. I'll compute it under 2 different scenarios, one where the tax shield on interest is considered, and one where its not considered (since you've requested for the tax shield on interest cost to not be considered for computation of NPV.

Year	1	2	3	4	5	6	7	8	9	10
Sales	200,000.00	1,000,000.00	2,000,000.00	2,080,000.00	2,163,200.00	2,249,728.00	2,339,717.12	2,433,305.80	2,530,638.04	2,631,863.56
Cost of Goods Sold @ 65%	(130,000.00)	(650,000.00)	(1,300,000.00)	(1,352,000.00)	(1,406,080.00)	(1,462,323.20)	(1,520,816.13)	(1,581,648.77)	(1,644,914.72)	(1,710,711.31)
Gross Profit	70,000.00	350,000.00	700,000.00	728,000.00	757,120.00	787,404.80	818,900.99	851,657.03	885,723.31	921,152.25
SGA Expenses	(50,000.00)	(250,000.00)	(500,000.00)	(520,000.00)	(540,800.00)	(562,432.00)	(584,929.28)	(608,326.45)	(632,659.51)	(657,965.89)
EBITDA	20,000.00	100,000.00	200,000.00	208,000.00	216,320.00	224,972.80	233,971.71	243,330.58	253,063.80	263,186.36
Depreciation	(180,000.00)	(126,000.00)	(88,200.00)	(61,740.00)	(43,218.00)	(30,252.60)	(21,176.82)	(14,823.77)	(10,376.64)	(7,263.65)
EBIT	(160,000.00)	(26,000.00)	111,800.00	146,260.00	173,102.00	194,720.20	212,794.89	228,506.81	242,687.16	255,922.71
Interest Cost	(21,000.00)	(21,000.00)	(21,000.00)	(21,000.00)	(21,000.00)	(21,000.00)	(21,000.00)	(21,000.00)	(21,000.00)	(21,000.00)
EBT	(181,000.00)	(47,000.00)	90,800.00	125,260.00	152,102.00	173,720.20	191,794.89	207,506.81	221,687.16	234,922.71
Tax Cost A (considering interest cost as deductible)	-	-	9,988.00	19,415.30	23,575.81	26,926.63	29,728.21	32,163.56	34,361.51	36,413.02
Tax Cost B (not considering interest as deductible)	-	-	12,298.00	22,670.30	26,830.81	30,181.63	32,983.21	35,418.56	37,616.51	39,668.02
Profit after tax (considering tax Cost A)	(181,000.00)	(47,000.00)	100,788.00	144,675.30	175,677.81	200,646.83	221,523.10	239,670.37	256,048.67	271,335.73
Profit after tax (considering tax Cost B)	(181,000.00)	(47,000.00)	103,098.00	147,930.30	178,932.81	203,901.83	224,778.10	242,925.37	259,303.67	274,590.73
Cash flow A after adding back depreciation (to Tax Cost A)	(1,000.00)	79,000.00	188,988.00	206,415.30	218,895.81	230,899.43	242,699.92	254,494.14	266,425.31	278,599.38
Cash flow B after adding back depreciation (to Tax Cost B)	(1,000.00)	79,000.00	191,298.00	209,670.30	222,150.81	234,154.43	245,954.92	257,749.14	269,680.31	281,854.38
Working Capital (@5% of Sales)	(10,000.00)	(50,000.00)	(100,000.00)	(104,000.00)	(108,160.00)	(112,486.40)	(116,985.86)	(121,665.29)	(126,531.90)	(131,593.18)
Net Cash (Cash Flow A)	(11,000.00)	29,000.00	88,988.00	102,415.30	110,735.81	118,413.03	125,714.06	132,828.85	139,893.41	147,006.20
Net Cash (Cash Flow B)	(11,000.00)	29,000.00	91,298.00	105,670.30	113,990.81	121,668.03	128,969.06	136,083.85	143,148.41	150,261.20

Now let's move on to computing the NPV. Also, the working capital released at the end of the project has not been considered in the above income statement computation.

The initial outlay & the terminal cashflow has been computed below:

Year	1
Outlay	600,000.00
Additional Working Capital	10,000.00
Total Initial Outlay	610,000.00

Year	10
Sale value	16948.51
Tax liability	0
Working Capital released	131,593.18
Total terminal cash flow	148,541.69

NPV & IRR calculations are given below:

Year	0	1	2	3	4	5	6	7	8	9	10 (including terminal cash flow)	NPV@20%	IRR
Net Cash (Cash Flow A)	(610,000.00)	(11,000.00)	29,000.00	88,988.00	102,415.30	110,735.81	118,413.03	125,714.06	132,828.85	139,893.41	295,547.90	(273,160.33)	9%
Net Cash (Cash Flow B)	(610,000.00)	(11,000.00)	29,000.00	91,298.00	105,670.30	113,990.81	121,668.03	128,969.06	136,083.85	143,148.41	298,802.90	(265,033.62)	10%

you have the profitability index here:

Year	0	1	2	3	4	5	6	7	8	9	10 (including terminal cash flow)	PI
Discounted Net Cash Flow A	(610,000.00)	(9,166.67)	20,138.89	51,497.69	49,390.09	44,502.24	39,656.28	35,084.49	30,891.74	27,112.28	47,732.64	0.55

The cash flows for PI have been used considering interest cost as tax deductible. I've done the same thing for Payback Period & Discounted payback period below:

Year	0	1	2	3	4	5	6	7	8	9	10 (including terminal cash flow)
Cumulative cash flows	(610,000.00)	(621,000.00)	(592,000.00)	(503,012.00)	(400,596.70)	(289,860.89)	(171,447.86)	(45,733.79)	87,095.05	226,988.46	522,536.36

Payback period is given by: Last year in which cumulative cash flow is negative + (Cash flow in that year/Sum of Cash flows in this year & the next year)

This equation would give us the following:

= 7+(45733.79/132828.85) = 7.34 years.

Now let's move on to discounted payback period, the same logic applies as above:

Year	0	1	2	3	4	5	6	7	8	9	10 (including terminal cash flow)
Cumulative discounted cash flows	(610,000.00)	(619,166.67)	(599,027.78)	(547,530.09)	(498,140.00)	(453,637.76)	(413,981.48)	(378,896.99)	(348,005.24)	(320,892.96)	(273,160.33)

But what do we have here! The discounted cash flows never actually achieve the positive cash flows!

That's why we are going to be unable to compute discounted payback period.

I copied & pasted everything straight from excel like you requested. Feel free to reach out for anything you need further help on in the comments section!

Don't forget to upvote!