Fix the many mistakes made by the analyst who put together the Colonel Electric spreadsheet that is shown . Make whatever assumptions you feel are appropriate; just be sure to footnote them.
The five issues are:
1) treatment of sunk cost;
2) should use normal price and cost;
3) treatment of financing costs (interest);
4) add back depreciation in cash flow computation;
5) opportunity cost of using existing space (assume the PV of it is 11) and opportunity benefit from rents (assume the PV of it is 3). Please note that whatever assumption used in your computation should be recorded in footnote
120 Cash Flows & Present Value Analysis of Proposed Investment Projected Fiscal Years Ended December 31 INCOME 2007 2008 2009 2010 2011 2012 2013 2014 2015 Revenue (a) $0.0 $ 80.0 $ 160,0 $ 400.0 $ 400.0 $ 400.0 S 400,0 $ 400.0 $ 400.0 Operating costs (b) (20) (50) (90) (210) (210) (210) (210) (210) (210) Interest (c) 0.0 (18) (24) (42) (42) (42) (42) (42) (42) Pretax Income (20.0) 46.0 148.0 148.0 148.0 148.0 148.0 148.0 Tax (d) 0.0 (5.4) (44.4) (44.4) (44.4) (44.4) (44.4) (44.4) Net Income ($20.0) $12.0 $40.6 $103.6 $103.6 $103.6 $103.6 $103.6 $103.6 CASH FLOW Net Income ($20.0) $12.0 $40.6 $103.6 S103.6 $103.6 $103.6 $103.6 $103.6 Capital Expenditures (e) (80.0) 0.0 0.0 0.0 Working Capital (40.0) (40.0) (120.0) 0.0 0.0 0.0 200.0 Requirements (6) Net Cash Flow ($140.0) ($28.0) (579.4) $103.6 $103.6 $103.6 $103.6 $103.6 S303.6 Net Present Value @ 15% $137.5| NOTES: (a) Sales of 20 units in 2008, 40 in 2009, and 100 per year through 2015. 2007 unit price of $4.00 assumed to grow at inflation rate of 3% per year. (b) 2007 operating costs of $2.0 per unit assumed to grow at 3% inflation rate. Also includes depreciation charges of $10 million annually beginning in 2008. 2007 figure reflects 2003 expenditure of $18.2 million on R&D, equivalent to $20 million in 2007 dollars (C) Charged on capital expenditure and working capital at current 15% cost of capital (d) Assumed tax rate is 30%. Tax loss in 2007 carried forward to 2008 and 2009 (e) $60 million for new machinery, $20 million for warehouse extension. Full cost of extension charged to this project, although only half of extension will be used initially. No charge made for land and building, as new machinery will be housed in existing space. (1) Assume production costs (including raw materials, labor costs, etc.) are occurred in the previous year. 120 Cash Flows & Present Value Analysis of Proposed Investment Projected Fiscal Years Ended December 31 INCOME 2007 2008 2009 2010 2011 2012 2013 2014 2015 Revenue (a) $0.0 $ 80.0 $ 160,0 $ 400.0 $ 400.0 $ 400.0 S 400,0 $ 400.0 $ 400.0 Operating costs (b) (20) (50) (90) (210) (210) (210) (210) (210) (210) Interest (c) 0.0 (18) (24) (42) (42) (42) (42) (42) (42) Pretax Income (20.0) 46.0 148.0 148.0 148.0 148.0 148.0 148.0 Tax (d) 0.0 (5.4) (44.4) (44.4) (44.4) (44.4) (44.4) (44.4) Net Income ($20.0) $12.0 $40.6 $103.6 $103.6 $103.6 $103.6 $103.6 $103.6 CASH FLOW Net Income ($20.0) $12.0 $40.6 $103.6 S103.6 $103.6 $103.6 $103.6 $103.6 Capital Expenditures (e) (80.0) 0.0 0.0 0.0 Working Capital (40.0) (40.0) (120.0) 0.0 0.0 0.0 200.0 Requirements (6) Net Cash Flow ($140.0) ($28.0) (579.4) $103.6 $103.6 $103.6 $103.6 $103.6 S303.6 Net Present Value @ 15% $137.5| NOTES: (a) Sales of 20 units in 2008, 40 in 2009, and 100 per year through 2015. 2007 unit price of $4.00 assumed to grow at inflation rate of 3% per year. (b) 2007 operating costs of $2.0 per unit assumed to grow at 3% inflation rate. Also includes depreciation charges of $10 million annually beginning in 2008. 2007 figure reflects 2003 expenditure of $18.2 million on R&D, equivalent to $20 million in 2007 dollars (C) Charged on capital expenditure and working capital at current 15% cost of capital (d) Assumed tax rate is 30%. Tax loss in 2007 carried forward to 2008 and 2009 (e) $60 million for new machinery, $20 million for warehouse extension. Full cost of extension charged to this project, although only half of extension will be used initially. No charge made for land and building, as new machinery will be housed in existing space. (1) Assume production costs (including raw materials, labor costs, etc.) are occurred in the previous year
