G33 X V fx A B C D E G H K M N P Q R S T U V W X Z AA Input Area Cost of Land 2,550,000 NOTE: DO NOT CHANGE THESE REFERENCE VALUES. When using Goal Seek or Solver to change the value Cost of Buildings & Equipment 4,300,000 of any of these initial values, be sure to reset it to the reference value afterward, before doing any Annual Depreciation (straight line, not CCA) 860,000 further analysis Life ofProject (Years) Sensitivity % Initial value reference value Terminal Value of Land 3,040,000 0% 3,040,000 3,040,000 Terminal Value of Buildings & Equipment 1,290,000 0% 1,290,000 1,290,000 First Year Salmon Sales (Ibs) (Q) 380,000 0% 380,000 380,000 Price per Pound (P) 8.50 0% 8.50 8.50 Unit Sales Growth Rate (G) 8.0% 0% 8% 8% Variable Costs as a percentage of Sales (V%) 35.0% 0% 35% 35% Fixed Costs (FC) 297,000 0% 297,000 297,000 Tax Rate (T) 35% WACC (R) 8% Model Year O Year 1 Year 2 Year 3 Year 4 Year 5 Initial outlay (6,850,000) Sales 3,230,000 3,488,400 3,767,472 4,068,870 4,394,379 Variable Costs 1,130,500 1,220,940 1,318,615 1,424,104 1,538,033 Fixed Costs 297,00 297,000 297,000 297,000 297,000 Taxable Cash Flows 1,802,500 1,970,460 2,151,857 2,347,765 2,559,347 Taxes $30,875 689,661 753,150 821,71 895,771 Depreciation Tax Benefit 301,000 301,000 301,000 301,000 301,000 Annual After-Tax Cash Flow 1,472,625 1,581,799 1,699,707 1,827,047 1,964,575 After-Tax Terminal Cash Flow 3,707,000 Total Annual Cash Flows -6,850,000 1,472,625 1,581,799 1,699,707 1,827,047 5,671,575 Net Income 612,625 721,799 839,707 967,047 1,104,575 Net Present Value Total Net Income Accounting Breakeven Goal Seek Formula Units Quantity BE =(FC+D)/(P-v), where v is variable cost per unit or P*(V%) Financial (EVA) Breakeven Goal Seek Formula Units ((1-T)*(D+FC)+CCOAAAD)/((1-T)*(1-V%)*P) cost of capital over and above allowed depreciation $223,745 annuity pmt - depreciation where reciation where annuity pmt = NPV of initial and terminal capital cash flows (CCOAAAD Year 1 Year 2 Year 3 Year 4 Year 5 degree of operating leverage DOL=1+(FC/OCF) For the 3 estimates below, assume that all earnings are retained and that the company's target D/E is 0.5. Do these calculations for year 1 only. external financing needed (EFN)= (growth rate X net fixed assets) - Aretained earnings 48 internal growth rate = pSR/(NFA-pSR) sustainable growth rate = p(S/NFA)(1+D/E)R/[1-p(S/NFA)(1+D/E)R]