Conch Republic

Input Area (expand as needed): Selling price per unit 247.5 VC per unit 97 Fixed cost per year 4200000 Discount rate 19% Tax rate 35% Number of years for the project 5 Model Area (expand as needed): Initial Investments Equipment Purchase 20500000 Working Capital 3000000 Total Investment 23500000 Salvage value 3500000 Annual Net After Tax Cash Flows year 0 year 1 year 2 year 3 year 4 year 5 Initial Investment -23500000 Estimated Sales volume 68000 79000 105000 73000 50000 Sales 16830000 19552500 25987500 18067500 12375000 Variable cost 6596000 7663000 10185000 7081000 4850000 Fixed cost 4200000 4200000 4200000 4200000 4200000 Depreciation 3400000 3400000 3400000 3400000 3400000 Net Income 2634000 4289500 8202500 3386500 -75000 Less: Tax 921900 1501325 2870875 1185275 -26250 Net Income after tax 1712100 2788175 5331625 2201225 -48750 Cash Flow 5112100 6188175 8731625 5601225 3351250 Terminal Year Cash Flows Working capital recovery 3000000 Salvage value 3500000 Yearly cash flow 3351250 Terminal year cashflow 9851250 Alter nothing below this line; area above should be expanded as needed to create a complete model year 0 year 1 year 2 year 3 year 4 year 5 Net Project CF -23500000 5112100 6188175 8731625 5601225 9851250 8 Grading Criteria Cumulative CF -23500000 -18387900 -12199725 -3468100 2133125 11984375 2 Item Pts Score Comments Input area format 8 8 Results Area: Model area format 8 8 Payback period 3.62 years —> note: formula OR number okay here 4 Calc initial invest 8 8 PI 0.902 4 Calc annual OCF 8 8 IRR 14.12% 4 Calc terminal year 8 8 MIRR 16.10% 4 Net Project CF 8 8 NPV ($2,295,332.62) 4 Cum CF 2 2 Results area 20 20 Question 1 6 6 Questions Question 2 6 6 1) Using your spreadsheet model, indicate how much change in NPV occurs with a +1% increase in the discount rate. Question 3 4 4 Question 4 2 2 Question 5 12 12 NPV (at 19%) -2295333 —> note: type number here,