MIS
388
Decision
Support Exercise
Your client, Hilltop Limited Partners, has just hired you to
develop a financial analysis of a proposed business venture. Specifically, they need you to create an Excel
worksheet with which they can evaluate various alternative development plans
for a 125-acre site on a hilltop in central
They are planning for a mixed-use project called the Hilltop Wonderland. Due to restrictions in zoning and availability of utilities, Hilltop Wonderland (HW) will be composed of no more than 10 restaurants, no more than 6 clubs, a hotel not to exceed 200 rooms, various fun-rides, and souvenir shops.
Through your discussions with the engineers, site use planners and the various partners, you have determined the following:
The average fun-ride will cost $350,000 to build. Maintenance and repair costs average about $10,000 a year for each ride. It requires three minimum wage people to operate a ride. Initially, it is anticipated that the rides would be open eight hours per day, five days a week, 52 weeks per year. It is also anticipated that tickets to ride will cost $2.00 and that the number of riders will be related to the total number of visitors in the hotel, restaurants, and bars combined (assume 8%).
The hotel can be built for $50,000 per room plus an additional $3,000,000 for ballrooms and lobbies. Average room rates would be $100 per night. Utilities, cleaning, and other operating costs should be approximately 85% of revenues plus a fixed monthly expense of $100,000. The partners are hoping for a 20% occupancy rate the first year with an average of 1.3 people to a room.
The souvenir shops will be movable carts that cost $55,000 each. The average purchase made in a shop is $25 and the average profit margin is 35 percent after subtracting the cost of the items and the salesperson’s wages. It is anticipated that 10% of the HW visitors (combined total of hotel, restaurant, & club customers) will make purchases in the souvenir shops.
The number of restaurants and clubs is also limited to the total amount of building space available. A restaurant will occupy 12,500 square feet of land area. A club occupies 9,000 square feet. There is a total land area available for clubs and restaurants of 135,000.
The first year a restaurant serves 75 customers a day at an average net profit of $17.50 per customer after deducting food, labor, and other operating costs. A club serves 60 customers a day at an average net profit of $27.50.
Construction costs for a restaurant are $2,500,000 and $1,800,000 for a club.
You should develop an Excel workbook that the investor/partners can use to study the financial impact of various alternative mixes of number of clubs, restaurants, shops, rooms, rates or any other factor. Your workbook should follow the design standards we have discussed. It should be well documented so that someone using it knows what data should be entered. Validate the input data to prevent erroneous combinations of alternatives.
The planning horizon is 15 years. Your output should include a cash flow projection, net cash flow, net present value, payback analysis, and internal rate of return computation. Play with various alternatives yourself to test the computations and e-mail me your worksheet when you are done.
Other assumptions:
All construction and purchases of carts will be done in the first year before opening and no further construction is allowed after the construction has been approved by the zoning commissions.
The partners believe that the number of customers visiting the hotel, restaurants and clubs will increase by a percentage each year as follows:
15 percent per year through year5
10 percent per year through year 10
5 percent per year through year 15
This data should be set up in a table on the data input page and be referenced with a VLOOKUP from the cash flow or calculations page. In this way, the Partners could study differing percentages and differing time periods. Such as
25 percent through year 3
15 percent through year 8
5 percent through year 15
(Hint: look at how the “Grade Scale” is set up in the grade computation exercise we did in lab)
Turning in the assignment:
LEAVE THE SHEETS UNPROTECTED SO THAT I CAN GRADE YOUR EFFORTS. You may e-mail the .xls file with the subject “John Doe (9:99 class)– Decision Support homework” or give me a CD or flash memory stick labeled with your name and class information.