Answer should be on Excel spreadsheet This is a “Fast Food” outlet that would ex
ID: 2796789 • Letter: A
Question
Answer should be on Excel spreadsheet
This is a “Fast Food” outlet that would expect
1,060 customers per day. The establishment would be open 365 days a year serving from 6am till 11pm. The land would cost $300,000. The building would be valued at $4,000,000 depreciated over 20 years and $500,000 for assets that would have to be upgraded after every six years at the cost of $600,000 each time. Operating Expenses per year are estimated to be $1,500,000. The pricing structure will generate a contribution margin per customer $5.
1. Analyze the data and using Net Present Value, Profitability Index, Internal Rate of Return (use Excel’s PV and IRR functions), and the Payback Method. Use a discount rate of 4% and a 33% tax rate.
Explanation / Answer
Land Cost
300,000
Building Cost
4,000,000
Asset Cost
500,000
Discount Rate
4%
Tax Rate
33%
Depreciation
200000
Straight Method
Operating Expense
1,500,000
Revenue
1934500
1060 Customer/Day for 365 at $5 margin
Initial Cash Outflow
-4,800,000
Yearly Cash Inflow
1934500
Yearly Cash Outflow
-1,700,000
Net Yearly Cash Inflow
234,500
PV of Net Yearly CF
3186932
PV of New Asset
600,000
Year 6
-317706
Using (1 - Tax Rate) assuming debt financing
Year 12
-251088
Using (1 - Tax Rate) assuming debt financing
Year 18
-198439
Using (1 - Tax Rate) assuming debt financing
Total PV of Asset
-767233
NPV
-2380301
IRR is negative for the project, so using Modified IRR approach
MIRR = (FV of Sum of all cash Inflows/PV of Sum of Cash out flows)^1/20 – 1
MIRR = (234500*(1.04^20)/4,800,000+767233))^(1/20) - 1
MIRR = -11%
Profitability Index = 1 + NPV/Initial Investment = 1 + (-2380301/-5567233) = 1.42
Pay Back Period = Investment /Future Cash Inflows = -5567233/-2380301 = 23.4 years
Based on its values this is project not a good recommendation for investment.
Land Cost
300,000
Building Cost
4,000,000
Asset Cost
500,000
Discount Rate
4%
Tax Rate
33%
Depreciation
200000
Straight Method
Operating Expense
1,500,000
Revenue
1934500
1060 Customer/Day for 365 at $5 margin