Conch Republic

Only available on StudyMode
  • Download(s) : 423
  • Published : January 27, 2013
Open Document
Text Preview
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, not...
tracking img