Problem Text

The Excel file provided $($see link on Canvas assignment$)$ lists customers, rooms they occupied, arrival and

departure dates, number of occupants, and daily rate for the Durham Inn, a small bed$-$and$-$breakfast inn, during

one month. Room rates are the same for one or two guests; however, additional guests must pay a surcharge of

$$25$ per person per day $($this covers additional housekeeping, laundry, and breakfast costs$).$ Parties staying for

more than five days receive a $8\%$ discount on the room rate and any guest surcharges. First make a copy of the

data sheet. Then build a model to calculate the revenue from each guest, and the total revenue for the month.

Instructions & Guidelines

$$ Flexible Model: Make the model logic flexible. Store the parameter values $($e$.$g$.,$ guest threshold, surcharge

amount, discount threshold, discount percentage$)$ in separate cells, and make sure the user can change any

of the values of these value, with the computed results remaining correct. Also, if any of the given data

changes $($for example, we paste in a different month$$s guest stays$),$ the computed results need to update

properly. Break the logic into small chunks $($use multiple new columns$)$ to create the necessary logic. The

right$-$most column should be the total revenue for that guest. Write one formula for the top cell in each

column and copy the formula down $($if your logic doesn$$t work for all cases, it means your logic is not

correct$).$ It is not required, but I encourage you to convert the data to a Table first, and then use structured

$($i$.$e$.,$ table$-$based$)$ formulas in Excel. Doing this forces you to make sure the logic works for every row in the

table. It is best to convert to a Table first before you start writing formulas.

$$ Base Case Check Value: As a check on your work, calculate the total revenue. Using the base case values of

the parameters, the revenue should be $$25,398\mathrm{.}28.$

$$ Confirm flexibility $($Scenario A$)$: Once you have the base case correct, experiment with different values of the

parameters. As a "check value" on your work, change the parameter values to the following: surcharge

applies to more than $3$ guests $($instead of $2)$; surcharge amount is $$30($instead of $$20)$; discount for stays

longer than $7$ days $($instead of $5$ days$)$; and discount amount of $20\%($instead of $8\%).$ With these parameter

values, the calculated revenue should be $$24,146\mathrm{.}60.$ If you're not getting this, it probably means your logic

is not sufficiently flexible. Check your logic to make sure it is correct, and that you haven't buried any of the

base case parameter values inside the formulas $($these are the most common errors, but not the only

possible ones$).$

$$ Analysis $($Scenario B$)$: Suppose the hotel has a goal to have revenue be $$27,000$ from this pattern of room

demand in the future. Reset all the parameter values back to their base case values. Then find the per$-$guest

$($per day$)$ surcharge amount for number of guests over $2$ that would be needed to generate $$27,000$ from

this demand pattern.