Please work trough the case and answer the questions with formulas

Thanks

Residence Inn: In-House vs. Out-Sourced F&B Capital Budgeting | NPV | IRR | PMI Team Date Team Members Present Background Michelle has been working in the finance department of her company for 2 years. Yesterday her boss Janet informed her that they were wrapping up a final proposal on a new Residence Inn in Chesterfield, a tertiary market of St. Louis, Missouri. The original plans included a small kitchen and staging area to be used by outside caterers to deliver and prepare food for the meeting space. Janet was starting to question whether or not outsourcing was the best alternative. Consequently, she asked Michelle if she could prepare 2 capital budgets comparing the costs and benefits of both in-house and out-sourced catering for the new property. To help Janet get started she provided the information below gathered from other company-owned hotel franchises. 1: Determine the average annual revenue per square feet for hotels that provide in-house or outsourced catering services. (4 pts) Midwestern hotels in tertiary markets Hyatt Place Hyatt House Hilton Garden Inn Embassy Suites Hampton Inn Courtyard by Marriott Residence Inn Springhill Suites Weighted Average In-House Weighted Average Out-Sourced Revenue Assumptions Total Meeting Space (Sq Feet) Revenue Growth Rate (Years 2 through 4) Revenue Growth Rate (Years 5+) Revenue from food Revenue from room rental and AV In House Catering Sq Feet Annual Revenue Annual Revenue/Sq Foot Y N Y Y N N N Y 3400 1800 3200 6800 2400 3500 2900 4100 $450,000 $117,000 $377,000 $888,000 $152,000 $174,000 $191,000 $509,000 $132.35 $65.00 $117.81 $130.59 $63.33 $49.71 $65.86 $124.15 In-House 3500 10% 4% 65% 35% Out Sourced 3500 10% 4% 30% 70% Cost Assumptions Depreciable Assets (3 Years) Depreciable Assets (7 Years) Salvage value of all assets at time 7 Food Cost (as a % of food revenue) Executive Chef Salary Events Manager Salary Service Staff Wages (as a % of total revenue) Kitchen Staff Wages (as a % of food revenue) Other Operating Costs (as a % of total revenue) Working Capital Needed (Time 0 to 6) In-House $80,000 $180,000 $20,000 32% $40,000 $35,000 10% 12% 15% $20,000 Out Sourced $60,000 $70,000 $10,000 0% $0 $35,000 18% 0% 18% $10,000 2: Using the assumptions above, complete the capital budget below for the hotel with in-house catering; determine the NPV, IRR and the PMI. (6 pts) Capital Budget for F&B w/ In-House Catering Year 0 1 2 3 4 5 6 7 Capital Items Capital Investment Salvage Value Capital Gains Tax Investment-Related Cash Flow 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Operating Items Revenue Operating Costs or Expenses Depreciation Expense Taxable Income Income Tax Working Capital Change in Working Capital Operating Cash Flow 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Free Cash Flow 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Present Value of FCF NPV IRR PMI Additional Calculations Required Tax Basis Additional Input Items Ordinary Tax Rate Capital Gains Tax Rate Discount Rate 35% 15% 10% 3: Using the assumptions above, complete the capital budget below for the hotel with out-sourced catering; determine the NPV, IRR and the PMI. (6 pts) Capital Budget for F&B w/ Out-Sourced Catering Year 0 1 2 3 4 5 6 7 Capital Items Capital Investment Salvage Value Capital Gains Tax Investment-Related Cash Flow 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Operating Items Revenue Operating Costs or Expenses Depreciation Expense Taxable Income Income Tax Working Capital Change in Working Capital Operating Cash Flow 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Free Cash Flow 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Present Value of FCF NPV IRR PMI Additional Calculations Required Tax Basis Additional Input Items Ordinary Tax Rate Capital Gains Tax Rate Discount Rate 35% 15% 10% 4: What is your recommendation? Explain the basis for your decision. (4 pts)