Question

Please show all details and you can use excel to build some tables.

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  $200,000

year two $1,000,000

year three $2,000,000

                                                                                   

Starting year four, your sales will grow at a rate of 3 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.

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. You pay no provincial tax in the first three years of operations. After that, you start paying provincial tax at a rate of 4.5 percent. 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 need you to build the rest of the income statement and CCA schedule in Excel, and copy/paste them into your deliverable.

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.

depreciation schedul                year one

book value starting balance   $600,000

depreciation                             $90,000

book value ending balance      $510,000

[1] If you use CCA formula, your payback and discounted payback will be slightly different than if you include depreciation in the income statement.

[2] If you are interested to learn how Canadian provinces impose barriers on beer trade from other provinces, check out www.realbeer.com/library/authors/hughey-r/distribution.php.

[3] Reduced federal income tax rate of eleven percent applies to small business income up to $500,000. The limit was raised from $400,000 as of 2009. See www.cra-arc.gc.ca/tx/bsnss/tpcs/crprtns/rts-eng.html.

[4] New small business tax deduction effectively eliminated the Nova Scotia corporate income tax for the first three taxation years of a new small business after incorporation. The corporation must apply each year to the Nova Scotia Minister of Finance for a Nova Scotia Tax Deduction Eligibility Certificate. Check www.novascotia.ca/finance/en/home/taxation/businesstax/corporateincometax/newsmallbusinesstaxdeduction/default.aspx.

[5] Corporate income tax rate of 4.5 per cent applies up to the Nova Scotia business limit of $400,000 in taxable earnings. This rate is also known as the Small Business Rate and applies to all Canadian-controlled private corporations with limited taxable capital. You may wish to check out www.novascotia.ca/finance/en/home/taxation/businesstax/corporateincometax/default.aspx.

[6] Industry Canada - www.ic.gc.ca - FAQ says that maximum fixed rate on a small business loan equals the lenders' single family residential mortgage rate plus three percent. Currently, five-seven year mortgages are offered at approximately four percent, so the rate on the loan is set at seven percent.

Answer 1

	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10
Purchse of Equipment	(600,000)
Net Sales		200,000	1,000,000	2,000,000	2,060,000	2,121,800	2,185,454	2,251,018	2,318,548	2,388,105	2,459,748
Cost of Sales		(130,000)	(650,000)	(1,300,000)	(1,339,000)	(1,379,170)	(1,420,545)	(1,463,161)	(1,507,056)	(1,552,268)	(1,598,836)
General & Admin Exp		(50,000)	(250,000)	(500,000)	(515,000)	(530,450)	(546,364)	(562,754)	(579,637)	(597,026)	(614,937)
Immediate Working Capital	(10,000)	(10,000)	(50,000)	(100,000)	(103,000)	(106,090)	(109,273)	(112,551)	(115,927)	(119,405)	(122,987)
Scrap Value of Equipment											16,949
Provincial Tax @ 4.5%	-	-	-	-	(6,492)	(7,603)	(8,473)	(9,177)	(9,766)	(10,280)	(10,742)
Net Cash Flow	(610,000)	10,000	50,000	100,000	96,508	98,487	100,800	103,374	106,161	109,126	129,194
Discount Rate @ 20%	1.0000	0.8333	0.6944	0.5787	0.4823	0.4019	0.3349	0.2791	0.2326	0.1938	0.1615
Discounted Cash Flow	(610,000)	8,333	34,722	57,870	46,541	39,580	33,758	28,850	24,690	21,149	20,866
Net Present Value	(293,641)
Payback Period	7 years & 6 Months
Discounted Payback Period : Can not be calculated as the Net Present Value of the project is negative at the end of 10 year
Profitability Index	-48.94%	(NPV/Initial Investment)
Tax Computation
	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6	Year 7	Year 8	Year 9	Year 10
Purchse of Equipment
Net Sales		200,000.00	1,000,000.00	2,000,000.00	2,060,000.00	2,121,800.00	2,185,454.00	2,251,017.62	2,318,548.15	2,388,104.59	2,459,747.73
Cost of Sales		(130,000.00)	(650,000.00)	(1,300,000.00)	(1,339,000.00)	(1,379,170.00)	(1,420,545.10)	(1,463,161.45)	(1,507,056.30)	(1,552,267.99)	(1,598,836.03)
General & Admin Exp		(50,000.00)	(250,000.00)	(500,000.00)	(515,000.00)	(530,450.00)	(546,363.50)	(562,754.41)	(579,637.04)	(597,026.15)	(614,936.93)
CCA		(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)
Profit		(160,000.00)	(26,000.00)	111,800.00	144,260.00	168,962.00	188,292.80	203,924.94	217,031.04	228,433.82	238,711.12
Provincial Tax @ 4.5%		-	-	-	6,491.70	7,603.29	8,473.18	9,176.62	9,766.40	10,279.52	10,742.00
Scrap Value of Equipment
Purchase Value	600,000.00
Total CCA Value	(583,051.49)
Scrap/Sale Value	16,948.51