what wouod thr investors ROi for thhis 5 year project if the restaraunt achieved its budgeted operation results for thr year?
can you show me the excel formulas as well thank you :)
THE VALUE OF MONEY APPLICATION Siesta Restaurant Operating Results (000) 2002 2003 2004 2005 2006 5900 350 1.250 $925 30 $950 $975 165 $1,000 375 1,375 1.25 1,315 1,345 Revenue Food Beverage Total Operating expenses Food cost Beverage cost Labor cost Travel Marketing Utilities Rent Total 240 285 270 54 300 615 57 650 120 685 60 120 120 40 70 10 160 1,240 162 1,290 163 75 165 1.372 80 150 1.405 1,332 Operating Profit (Loss) $10 $(5) (17) 627) $(30) Based on Paris's market analysis, tour of the competition, inspection of the subject property and interviews with the prior owner, she concludes a Star Restaurant would work in the subject space, but it would require approximately $200,000 of renovation and conversion cost in addition to the land purchase price of $2,000,000. By Year 5, the restaurant could get 52.5 million in annual food revenue and $1.5 million in annual beverage revenue Ms. Brown estimates the following cash flows for the first five years of operations, with cash flows leveling off in Year 5. Year -am Cash Flow $695,000 876,250 1,057,500 1,238,750 1,420,000 Calculate the IRR and NPV of this project utilizing a 12% discount rate and a 15% cap rate. Ms. Brown was able to secure a loan for $1.540,000, and an equity investor agreed to invest the remaining $660,000 in exchange for 20% ownership in the project. THE VALUE OF MONEY APPLICATION Siesta Restaurant Operating Results (000) 2002 2003 2004 2005 2006 5900 350 1.250 $925 30 $950 $975 165 $1,000 375 1,375 1.25 1,315 1,345 Revenue Food Beverage Total Operating expenses Food cost Beverage cost Labor cost Travel Marketing Utilities Rent Total 240 285 270 54 300 615 57 650 120 685 60 120 120 40 70 10 160 1,240 162 1,290 163 75 165 1.372 80 150 1.405 1,332 Operating Profit (Loss) $10 $(5) (17) 627) $(30) Based on Paris's market analysis, tour of the competition, inspection of the subject property and interviews with the prior owner, she concludes a Star Restaurant would work in the subject space, but it would require approximately $200,000 of renovation and conversion cost in addition to the land purchase price of $2,000,000. By Year 5, the restaurant could get 52.5 million in annual food revenue and $1.5 million in annual beverage revenue Ms. Brown estimates the following cash flows for the first five years of operations, with cash flows leveling off in Year 5. Year -am Cash Flow $695,000 876,250 1,057,500 1,238,750 1,420,000 Calculate the IRR and NPV of this project utilizing a 12% discount rate and a 15% cap rate. Ms. Brown was able to secure a loan for $1.540,000, and an equity investor agreed to invest the remaining $660,000 in exchange for 20% ownership in the project