The Continental hotel is situated in London. The hotel charges 250 per night per room. The hotel also has an excellent quality restaurant. From the following information that relates to the Hotel, you are required to prepare a month-by- month cash budget forecast for Apr 2022 to Mar 2023. On the 1st Apr 2022, the business bank account was in credit by 13,500. If the business is overdrawn on the last day of any month, the bank charges interest at the rate of 6.5% per month based upon the month end balance. The interest is withdrawn on the 10th day of the following month. The account is paid interest monthly on positive balances on the 15th day of each month at an interest rate of 3% based upon the balance at the end of the previous month. On the basis of past experience and taking account of predicted future developments in the market, the hotels management believes that the average nightly demand for the coming year will be: Month Predicted Revenue Apr 185,000

May 166,000

Jun 185,000

Jul 140,000 Aug 142,000 Sept 149,000 Oct 156,500 Nov 166,900

December 174,000

Jan 149,000 Feb 150,900

march 150,800 All room payments are received in the business account, as the hotel does not currently offer business credit facilities or payment plan to corporate guests. You are asked to calculate using four weekly calendar months. The hotel has twenty-five employees and the total monthly wage bill is 57,500. Over time is estimated at 3,400 per month. The hotel pays its utility bills monthly at an estimated cost of 2,000. Business rates are estimated at 3,200 per month, although no payments are made in September or May. For F&B, the hotel restaurant pays 27,800 each month, apart from Dec 2022, where 32,100 was allotted for Corporate booking and festive celebration. The Hotel subscribes to ten different magazines for use by the guests at a quarterly cost of 2,150. The first payment being due in Apr 2022. The cost of complimentary in-room beverages is estimated to cost the hotel 1,350 per month and is paid every two months starting from April 2022. Daily delivery of The Guardian, The Times, The Financial Times and The Telegraph newspapers are paid for in the month following delivery. The average monthly cost to the Hotel is 1,150 per month. The hotel pays annual building insurance at a cost of 5,500, which is due in September. In April 2022, the hotel purchases a custom-made wardrobe at a cost of 14,000, the payments are due in four instalments. The first instalment is due on May 30th 2022. Monthly laundry cost for Uniforms and house keeping of 1,625. A Corporate guest service training was conducted by UWTSD Swansea, however the invoice for this training have not yet been paid. 5 management staff attended at a cost of 1,500 per head. Cost to be paid in Feb 2023. The hotel has three vehicles on lease from Days, at a monthly finance cost of 2,750. The estimated cost of petrol for these vehicles is around 1,000 per month. Also, third party insurance is paid for the vehicles at a cost of 8,000 per year, which is due in December 2022. The hotel also has the vehicles valeted on a weekly basis at an estimate cost of 135 per week.

The hotel does not currently have a gym or swimming pool; however, guests are given a pass to use these facilities at Virgin Active which is situated opposite the hotel. The use of the facilities costs the hotel an estimate 2,500 per month and is paid on the 15th of each month. All rooms at the hotel have Sky Sports and Sky Movie available, which costs the Hotel an estimated 1,250 per month, which is due for payment on the 20th of each month. Also, the hotel has to pay a license to the Premier League to show live televised matches in public areas. The total annual cost of the license is 6,000 and is due in March 2023. Each room has a mini bar that includes a plethora of alcoholic beverages, soft drinks and snacks. The purchase of these items costs the hotel an average estimated cost of 7,000 per month which is payable the month following receipt. Guests are charged for each item used. The average estimated income from the sale of these items is 8,900 per month, which is received at the time of guest check out. Guests rarely stay at the hotel for more than a few nights at a time. Requirement Please use Excel template provided by your lecturer. Edit and prepare the budget planner (cash flow forecast) for the hotel for the forthcoming year. This should show the budgeted income and expenditure clearly categorized. It should also clearly show the net monthly income/expenditure and the opening and closing balance on the hotels bank balance. Work to the nearest , using the template provided. Assume 4 weeks in a calendar month.