Question:

You are a management accountant working in the Finance team at Miles private hospital. You have been assigned to assist the ENT (Ear, Nose and Throat) specialists1 that operate out ofrented premises on level 6 at the hospital. You are to prepare their master budget for 2021and make recommendations on how they could improve their operations.

There are three (3) ENT Specialists in the practice; Dr Janet Courtney, Dr Samantha Downerand Dr John Malouf. They are supported by two (2) administrative staff; Daniel Hall and JulieSky.

The following information has been gathered about the practice: ENT Clinic, John Flynn Hospital Balance Sheet As at 31 December 2020 Assets SAUD Cash 56,500 Accounts Receivable 48,895 ENT Equipment 1,560,000 Less: Accumulated Depreciation 190,000) Total Assets $1,475,395 Liabilities & Shareholder Equity Accounts Payable 56,400 Bank Loan 600,000 Retained Earnings 818,995 Total Liabilities & Shareholders' Equity $1,475,395 ENT Equipment: Depreciation on the ENT equipment totals $95,000 per year. Depreciation is recorded monthly. Bank Loan: The business has an interest only loan and the interest is paid monthly. The principle can be repaid at any time. The interest rate payable on the loan is 8% per annum. Expected patient visits for each of the ENT specialist for 2021 are indicated in the table below. 75% of the visits are expected to be half-hour appointments and the remainder will be one hour surgeries: Dr Janet Courtney | Dr Samantha Downer Dr John Malouf January 2021 90 80 95 February 2021 80 80 95 March 2021 80 80 95 April 2021 80 80 95 May 2021 70 80 95 June 2021 70 80 95 July 2021 50 80 95 August 2021 50 80 95 September 2021 40 80 95 October 2021 40 80 95 November 2021 40 80 95 December 2021 50 80 95 January 2022 40 80 95 February 2020 40 80 95The average fees received for the two [2] different types of appointments are $260 for half an hour [30 minutes} appointments and $400 for one hour surgeries [60 minutes). Cash collections: Sixty per cent {50%} of the revenue received for each month's Eth's servioes and surgeries is collected during the month when the services and surgeries are rendered and the remainder {40%} is collected the following month. The following table outlines the operating oosl's for the year. Operating Costs Payment Rent 51 DUO fmonih Paid monthly Utilities {Water 8: Electricity] $120! month Paid marten TelephoneI lnte met 3: IT support $200! month Paid monthly \"at\". Surgical supplies Repairs 3: maintenance of ENT STUD! quarter Paid March, June,r equipment - September 8: December Ea ch ENT Specialist's Individual Salary $300 000 {year Paid monthly excluding on-costs Ea ch Administrative Assistants Salary 57m 000 I year Paid monthly excluding on-costs _ Payments: It is estimated that 30% of all operating expenses will be paid in the month they are due and the remaining T056 will he paid in the following month. Salaries: The ENT specialists and administrative staff all receive an annual salary that is paid monthly. It is estimated that their on-costs2 are 30% of their annual salary. They work 3?.5 hours per week. Each employee's allowance for annual leave, public holidays and sick leave amounts to 3 weeks per year. They are also given an allowance for non-billable time of 2 hours per week which Includes professional develop meritI department meetings and idle time. Ignore GST and Income Tax. Using the information provided. prepare the following ope rating and nancial bud gets for the twelvemonth period from January 2601 to Deoember 2021. Operating Budgets: 1. Monthly Revenue Budget 2. Labour Budget [including labour cost per appointment type] 3. Monthly Operating Expense Budget Financial Budgets: 1. Budgeted Income Statement for the year ended 31 December 2D21 2. Monthly Cash flow Budget for the year ended 31 December 2021 3. Budgeted Balance Sheet for the year ended 31 December 2021 Your budget spreadsheet must be submitted in Excel Format [not as an appendix in your WEIRD document). The first worksheet should contain your rawfinput data and assumptions from this assignment oyeryiew sheet. All future worksheets should be linked to this data. Show all calculations and use Excel functions where possible. 1While you should use examples in your text as a guide. the key to using spreadsheets is that they are structured so that others can use them and follow the flow of information without diffi culty