The Sleepy Hollow Bed and Breakfast is an inn that is trying to determine what price it needs to charge for its rooms in order to break even this month because it is the summer the inn expects to rent rooms to 50 customers who will stay an average of two nights each over the course of the month. One meal is provided to each person that stays one night.

The Inn has the following operating costs:

Front Desk Salaries	$ 2,000	per month
Maintenance	$ 2,575	per month
Cleaning Staff	$ 8	per room
Depreciation	$ 1,500	per month
Food Costs	$ 5	per meal

The inn also has to pay for utilities, but they suspect that utilities are a mixed cost. Some past data on the cost of providing utilities is shown here:

Month	Person-Nights booked	Cost
Jul	125	$ 730.94
Aug	110	$ 598.95
Sep	90	$ 634.10
Oct	88	$ 554.82
Nov	34	$ 296.86
Dec	25	$ 233.97
Jan	10	$ 166.09
Feb	39	$ 339.30
Mar	78	$ 534.16
Apr	65	$ 489.02
May	81	$ 502.91
Jun	105	$ 608.50

Given the inn anticipates:

Customers	50
Nights per customer	2
Person-Nights	100

Part A) Use regression analysis to separate the fixed and variable part of this mixed cost. You can use excel to do this, but I highly recommend using another program such as R.

Intercept		Slope
Estimate		Estimate
Standard error		Standard Error
t statistic		t statistic

Fixed Cost Estimate
Variable Cost Estimate

Part B) What is the lowest price that Sleepy Hollow can charge for its rooms on average this month and still break even? Use cell references whenever possible.

Fixed Costs:

Variable Costs:

Total Fixed Costs:

Total Variable Costs:

Breakeven Price per room night:

Answer rating: 100% (QA)

Part A Separating fixed and variable costs using regression analysis Step 1 Prepare the data MonthPersonNights bookedCost Jul125 73094 Aug110 59895 Sep90 63410 Oct88 55482 Nov34 29686 Dec25 23397 Jan1... View the full answer