Question
You own a motel with 120 rooms. Fixed Daily cost for Mortgage is $1400. Other fixed costs (including staff salaries, maintenance) amount to $600 per
You own a motel with 120 rooms. Fixed Daily cost for Mortgage is $1400. Other fixed costs (including staff salaries, maintenance) amount to $600 per day. Variable cost per room is $20 per day (includes extra utility cost, room cleanup etc). Customers pay $70 per room per day. If more than 75 rooms are let out, you will need an extra employee to handle the additional crowd. This employee will cost $100 per day. There is also surcharge levied upon you based on the number of rooms rented if more than 20 rooms are rented. The surcharge is applied to the income from the room (not include room service income) If the number of rooms let out is between 20 and 40 (include 40), then 0.5% surcharge is levied. If the number of rooms let out is between 40 and 60 (include 60), then 1.5% surcharge is levied. If the number of rooms let out is between 60 and 80 (include 80), then 2.4% surcharge is levied. If the number of rooms let out is between 80 and 100 (include 100), then 3.7% surcharge is levied. If more than 100 rooms are let out, then 4.2% surcharge is levied. Also about 20% of the guests will request additional services (like laundry, extra mattress, etc) that amount to an average of $15.50 revenue per room. Those extra services will cost you $3.90 per room that orders the service. Build a business model using Excel. How many customers are needed for the business to breakeven? Do a two-way sensitivity analysis on number of rooms rent (from 40 to 120, on a 20 interval) and the percentage of customers use room service (from 10% to 20%, on a 2% interval) Do a two-way sensitivity analysis on number of rooms rent (from 40 to 120, on a 20 interval) and the additional employee cost (from current $100 to $350, on a $50 interval) Please save the excel as a PDF file and submit the PDF file
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started