Quantitative Methods for Business

The Jane is a boutique hotel located in the very heart of Manhattan, New York City. An historic hotel, The Jane offers travellers a unique experience in accommodation. Guests find The Jane unconventional although comfortable. Located on the doorstep of the colour and excitement the city has to offer, stylish boutiques, famous restaurants and a vibrant city nightlife are just minutes away. The Jane also offers bicycles for hire to guests who would like to cycle through the city.

Built in 1906-1908, The Jane was originally a hotel for sailors with cabin-style rooms, however over the years The Jane has become a New York City landmark. The Jane is touted as “… one of Manhattan’s quirkiest, hippest and cost-efficient places to stay, overlooking the Hudson River …” (www.newyork.com). Besides the unique guest rooms, the hotel has a ballroom and an ornate lobby featuring a large, mirrored disco ball, further reflecting the unconventional style of the hotel. Inside the hotel is Café Gitane, which locally sources organic produce and serves French-Moroccan cuisine.

Owners of The Jane are now looking to create a chain of Jane-inspired hotels around the world and are seeking cities that are small to navigate, have a cultural heritage and a cosy atmosphere. The list of cities under consideration includes Copenhagen, Zurich, Edinburgh, Auckland and Adelaide. The Adelaide-based hotel is to be called Jane’s Treasure, and as a part of the hotel consulting team, your job is to perform the quantitative analyses requested and prepare a business report for The Jane Consulting Group, in which you will need to describe your findings and make appropriate recommendations about the viability of Adelaide as the location of a new Jane hotel.
Your Friendly Concierge

Question 1: Is it profitable to invest in Adelaide?

The Treasury, located in the heart of Adelaide, will soon be available for lease and is considered a potential location for a Jane Hotel. To be re-named Jane’s Treasure if leased, the site possesses a combination of heritage and eclectic features in the spirit of the original Jane Hotel, including a Victorian-style lobby and underground tunnels. Other reasons supporting this location is its prominent location with easy access to Rundle Mall, the Central Market and public transportation, as well as being near the River Torrens, which offers a bike path directly to the beach.

Although the historical features of the proposed Jane’s Treasure building are suitable for a Jane-style hotel, The Jane Consulting Group would like to refurbish the hotel interior to recreate the unconventional atmosphere of the original Jane Hotel. It has been determined that a maximum of $15,000,000 will need to be borrowed to fund the refurbishment, as part of a 5-year commercial property loan based on 20-year amortisation, with monthly repayments. That is, the loan repayments are calculated as if the full 20 years will be taken to repay the loan, however under the terms of agreement, The Jane Consulting Group must repay the loan in full over a 5-year timespan. This means that there must be 59 equal repayments based on the 20-year amortisation schedule with the final 60th payment matching the remaining balance.
The secured commercial loan charges interest at 7.25% per year compounded monthly.
(a) (2 marks) Use EXCEL to calculate the monthly repayment The Jane Consulting Group would have to make on the commercial property loan, assuming a 20-year amortisation. Show the relevant output here.

The monthly payment required for the commercial property loan is $118,556.40.

(b) (8 marks) Use EXCEL to set up an Amortisation Schedule for the loan, assuming a 20-year amortisation. In this appendix, include the part of your completed EXCEL amortisation schedule corresponding to the first 5 years. To demonstrate the Amortisation schedule balances, also include the last 3 rows of payments. All amounts should be shown to 2 decimal places.

(c) (4 marks) Based on your amortisation schedule from part (b), what will be the final 60th payment on this loan? What is the total interest and total amount paid on the commercial property loan over the five-year period?

(d) (6 marks) To avoid the large final payment of the commercial property loan, you decide to seek out and present an alternative 5-year loan that requires at most, a monthly payment of $355,000, with The Jane Consulting Group indicating this amount to be an acceptable repayment size. You have a long-standing professional relationship with a domestic investment bank and believe you can negotiate the desired loan conditions, once you know the Annual Payment Rate needed.
Use EXCEL and the Goal Seek function to determine an appropriate interest rate for the proposed loan. Convert the answer provided by EXCEL to the equivalent Annual Payment Rate (APR), which is the total interest rate over a single year.
What is the total interest and total amount paid on this loan over the five-year period?
For full marks provide: (i) a copy of your Excel spreadsheet similar to the one below including your initials in the row names; and (ii) a copy of your ‘before’ and ‘after’ spreadsheets (before and after running Goal Seek) in this appendix. (Initials to be use: MKM, not BC)
EXCEL Goal Seek Instructions (below):
Mac Users: Tools Goal Seek.
Windows Users: Data  Data Tools  What-If Analysis in EXCEL 2007 and Excel 2010, or Tools  GoalSeek in EXCEL 2003.
Hint: in the output below, the repayment amount R has been entered in Excel using the appropriate formula for R from the Week 3 lecture notes. The information in the boxes will guide you on how to use Goal Seek so read carefully. Extra Hint: Do not use the PMT function to calculate R – use the formula for R.

(e) (6 marks) Finally, to determine whether it is profitable to invest in Adelaide, the Net Present Value (NPV) of the project is needed. As a conservative measure of the worthiness of the project, The Jane Consulting Group will only consider expanding to Adelaide if a net profit can be demonstrated halfway into the 5-year loan period.
The cash outflows for this investment include an immediate outlay of $435,000 to cover unpaid rates, permits and licenses as well as loan fees. Cash outflows commencing from month 1 include the monthly loan repayments, which you set at $255,000 to be conservative. Other monthly expenses that start from month 1 include salaries, estimated at approximately $195,000 per month. Utility payments (electricity, water, Council rates, etc.) occur quarterly and are estimated to cost $26,000 each quarter.
Cash inflow will be generated by leasing the hotel restaurant and bar, to generate monthly rental income. There will also be income from tourist accommodation, function and venue hire. The average projected monthly inflows are expected to commence from month 3 of the loan repayments and total $532,000 each month.
Using Excel, calculate the Net Present Value (NPV) of the cash outflows and inflows. The cost of capital is taken as 6.2% per year, compounded monthly.
For full marks provide: (i) a copy of your Excel spreadsheet that looks similar to the one below; and (ii) your calculations of the monthly cash flows for the first 12 months, the appropriate interest rate and the implementation of the NPV calculation. You can use Excel cell references to show your calculation if you prefer.
Zero marks: if the requested calculations are not shown, the spreadsheet presented will receive 0 marks.
Tip! See the Week 3 lecture notes for an example of using Excel to calculate the Net Present Value and the accompanying Excel spreadsheet from the Lecture Notes page on the course website to see how to compute the Net Present Value, as well as how to show this calculation using Excel cell references. (Initials to be use: MKM, not BC)

Question 2: I want to ride my bicycle …

Part of the charm of The Jane is that it offers guests the opportunity to experience their surrounds as a local by supplying bicycles and helmets, at a relatively low cost for hire. Guests can supplement their riding experience by requesting a market picnic basket to be supplied by the hotel that will fit on the back of a bicycle. The Jane Consulting Group would like to offer the same experience to travellers to Jane’s Treasure in Adelaide.
The cost of basic bicycle maintenance is fixed at $425 per week and it is assumed that approximately 10% of the total number of bicycles will require specialised maintenance each week, with an average cost of $55 per bike maintained. Weekly bicycle hire will generate $7 per bicycle, combining bicycle and helmet hire (a mandatory requirement). Approximately 60% of cyclists will also pay $10.50 for a market picnic lunch. The cost to the hotel for the picnic lunch will be absorbed by other income streams.
(a) (7 marks) What is the break-even number of bicycles to be hired each week? What is the monthly break-even profit assuming 4 calendar weeks in a month? Find the break-even point algebraically and illustrate it using an EXCEL graph (in bicycles hired per week). Include a copy of your EXCEL graph in Appendix 2. On your graph, identify the region corresponding to profits and the region corresponding to losses and the location of the break-even point.

EXCEL Instructions: Create a column called Number of bicycles and in that column enter values from 0 to 90 in increments of 5. Then create two more columns, one for Total Cost and another for Total Revenue. Enter appropriate formulae in EXCEL to obtain the total cost and total revenue corresponding to each value in the Number of bicycles column. Highlight the resulting three sets of numbers and go to Insert  Charts Scatter to obtain a graph. Make sure that your graph has been labelled appropriately (i.e. title, axis labels, legend) and that your chart title includes your network ID(macmk003).

(b) (4 marks) What number of bicycle rentals is required to obtain a profit of $1,000 per week? Present calculations to justify your answer. You can type them up in Word or use EXCEL.

(c) (5 marks) The layout of Jane’s Treasure will allow for 79 guest rooms, 85% of which will house 2 guests, with the remaining 15% single guest accommodations. Assuming the hotel is fully booked and that all guests hire a bicycle, is it possible to reach the desired target amount of $1,000 weekly profit from part (b)? If not, calculate the profit that can be achieved each week and then for one month, assuming a 4-week calendar month. In this appendix, present appropriate calculations to justify your answer. Type them up in Word or use EXCEL.

(d) (4 marks) To make the bike scheme as attractive as possible to guests, it was suggested that the cost of hiring a bike should be reduced from $7 to $5. What effect does this change have on the contribution margin based on the model used in part (a) and subsequently on the break-even number of bicycles and the break-even profit? Show your calculations.

Use the order calculator below and get started! Contact our live support team for any assistance or inquiry.