Example 9.1: Bay Resort

An investment of K1.4m in upgrading hotel facilities is largely influenced by the next 5 years expected occupancy rates for the additional bungalows, accommodating 30 persons, and the expected additional money each client spends in the resort. Management has determined that the additional revenue from the extra clients behaves like a uniform distribution between K120 and K300, depending on drinks and meals purchased. The occupancy rate however is more like a normal distribution, with a mean of 55% for the new bungalows, and a standard deviation of 20%. The two variables are independent of each other.

There are increased costs due to the upgrade, which have a fixed component of K65 per bungalow per night (due to maintenance and general expenses), and a variable cost of K25 per person per night.

Assume all figures are after tax, each new room is double occupancy (i.e. two beds per room), the cost of capital for the company is 10%. Calculate with 360 days per year.

Construct a model to illustrate the best, most likely, and worst-case scenario NPV. The most likely scenario uses the means of the two variables distributions, i.e. for the additional revenue per client use the mean of the uniform distribution and for the occupancy rate take the mean of the occupancy rate. To construct the best and worst case scenarios use the maximum / minimum values for additional revenue per client; for the occupancy rate assume a confidence interval of +/- one standard deviation.

Step 1 Open the worksheet 'Bay Resort' and enter the appropriate amounts in the input area. ( Below is an inserted image)

Step 2 In the parameter section enter the values for the two distributions and calculate the values for most likely, best and worst-case scenario. In the Scenario section start with the values for the most likely scenario. Calculate the estimated occupancy (in number of personsight).

Step 3 Calculate the revenues and costs. (N.B.: fixed costs apply to all bungalows, regardless of their occupation, variable costs only apply to the average number of guests).

Step 4 Calculate the additional net cash flow for each year and the resulting NPV.¹².

Step 5 Place the final NPV calculation in the Output Area.

Step 6 Use the Scenario Manager tool in Excel to define the various scenarios and create a summary report. The variables to be changed are the Additional revenue per client and the Occupancy Rate.

PARAMETERS No. of days per year No of persons per bungalow Additional Revenue/client best case most likely worst case \begin{tabular}{ll|} \hline OUTPUT \\ NPV \end{tabular} Occupancy Rate best case most likely worst case Cash Inflow Revenue Cash Outflow Capital Expense Operat. Cost fixed Operat. Cost variable Net CF NPV PARAMETERS No. of days per year No of persons per bungalow Additional Revenue/client best case most likely worst case \begin{tabular}{ll|} \hline OUTPUT \\ NPV \end{tabular} Occupancy Rate best case most likely worst case Cash Inflow Revenue Cash Outflow Capital Expense Operat. Cost fixed Operat. Cost variable Net CF NPV