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
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) specialists that operate out of rented premises on level 6 at the hospital. You are to prepare their master budget for 2021 and make recommendations on how they could improve their operations.
There are three (3) ENT Specialists in the practice; Dr Janet Courtney, Dr Samantha Downer and Dr John Malouf. They are supported by two (2) administrative staff; Daniel Hall and Julie Sky.
Below are the operating budgets so just need help with the Financial Budgets and i can do part B.
The following information has been gathered about the practice: ENT Clinic, Miles Private Hospital Balance Sheet As at 31 December 2020 Assets Cash Accounts Receivable ENT Equipment Less: Accumulated Depreciation Total Assets SAUD 56,500 48,895 1,560,000 (190,000) $1,475,395 Liabilities & Shareholder Equity Accounts Payable Bank Loan Retained Earnings Total Liabilities & Shareholders' Equity 56,400 600,000 818,995 $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 specialists 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 John Malouf 95 95 95 95 95 January 2021 February 2021 March 2021 April 2021 May 2021 June 2021 July 2021 August 2021 September 2021 October 2021 November 2021 December 2021 January 2022 February 2022 Dr Janet Courtney Dr Samantha Downer 90 80 80 80 80 80 80 80 70 80 70 80 50 80 50 80 40 80 40 80 40 80 50 80 40 80 40 80 95 95 95 95 95 95 95 95 95 The 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 (60%) of the revenue received for each month's ENT's services 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 costs for the year. Rent Utilities (Water & Electricity) Insurance Telephone, Internet & IT support General Office Expenses Surgical supplies Repairs & maintenance of ENT equipment Operating Costs $1 000 /month $120 / month $10 000/year $200 / month $200 /month $2600/month $700 / quarter Payment Paid monthly Paid quarterly Paid monthly Paid monthly Paid monthly Paid quarterly Paid March, June, September & December Paid monthly Each ENT Specialist's Individual Salary $300 000/year excluding on-costs Each Administrative Assistants Salary $70 000/year excluding on-costs Paid monthly Payments: It is estimated that 30% of all operating expenses will be paid in the month they are due and the remaining 70% will be 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-costs are 30% of their annual salary. They work 37.5 hours per week. Each employee's allowance for annual leave, public holidays and sick leave amounts to 8 weeks per year. They are also given an allowance for non-billable time of 2 hours per week which includes professional development, department meetings and idle time. Ignore GST and Income Tax. ASSIGNMENT REQUIREMENTS: PART ONE: Budget Schedules & Expected Outcomes for 2021 (15 marks/30 marks, weighted 50% of assignment total) Using the information provided, prepare the following operating and financial budgets for the twelve-month period from January 2021 to December 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 2021 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 WORD document). The first worksheet should contain your raw/input data and assumptions from this assignment overview sheet. All future worksheets should be linked to this data. Show all calculations and use Excel functions where possible. While 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 difficulty. PART TWO: Analysis & Report to the Specialists of the ENT Clinic (15 marks/30 marks, 50% of assignment total) Write a report (Maximum 1,500 words, 10% (+/-) will be accepted with no penalty) for the three specialists at ENT Clinic. The report should include: An overview of the expected results for the ENT clinic for the 2021 year including total revenue, operating expenses and expected cash position; a cost-volume-profit (CVP) analysis to indicate the breakeven point (in patients and revenue dollars) and provide recommendations regarding cost control strategies and how to improve the current margin of safety, an analysis of the business operations (i.e. current incomes and expenses) and recommendations regarding how to improve the profitability of the ENT Clinic. Use industry data (e.g. market rate for salaries) to support any recommendations for changes. Explain how the budgeting process can assist the ENT specialists to articulate decisions about the use of resources and how the budget can be used throughout the year to analyse variances and lead to continuous improvement within the clinic. Use visual aids such as tables and graphs to enhance your report presentation. REVENUE BUDGET Half hour One hour Dr Downer appointments surgeries 80 60 80 60 80 60 80 60 Half hour One hour appointments surgeries @ Monthly @ $260 $400 revenue 90 67.5 22.5 26,550.00 80 60 20 23,600.00 80 60 20 23,600.00 80 60 20 23,600.00 70 52.5 17.5 20,650.00 70 52.5 17.5 20,650.00 50 37.5 12.5 14,750.00 50 37.5 12.5 14,750.00 40 30 10 11,800.00 40 30 10 11,800.00 40 30 10 11,800.00 50 37.5 12.5 14,750.00 80 60 2021 Dr Courtney January February March April May June July August September October November December Total revenue per physician Monthly revenue Dr Malouf 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 Half hour Total appoint one hour Monthly monthly ments surgeries revenue revenue 95 71.25 23.75 28,025.00 78,175.00 95 71.25 23.75 28,025.00 75,225.00 95 71.25 23.75 28,025.00 75,225.00 95 71.25 23.75 28,025.00 75,225.00 95 71.25 23.75 28,025.00 72,275.00 95 71.25 23.75 28,025.00 72,275.00 95 71.25 23.75 28,025.00 66,375.00 95 71.25 23.75 28,025.00 66,375.00 95 71.25 23.75 28,025.00 63,425.00 95 71.25 23.75 28,025.00 63,425.00 95 71.25 23.75 28,025.00 63,425.00 95 71.25 23.75 28,025.00 66,375.00 80 60 80 60 60 60 80 80 80 80 60 60 80 60 $218,300.00 $ 283,200.00 $336,300.00 $837,800.00 MONTHLY OPERATING EXPENSE BUDGET Rent 1,000.00 Utilities 120.00 Insurance 833.33 Telephone, Internet, & IT support 200.00 General office expenses 200.00 Surgical supplies 2,600.00 Repairs & maintenance of ENT equipment 233.33 Total monthly operating expense $ 5,186.66 January February March April May June July August September October November December Total LABOUR BUDGET Labor cost of appointment Specialist Assistant On-costs of On-costs of (specialist) salary salary specialist assistants Total 78,175.00 25,000 5833.33 7500 1750 118,258.33 75,225.00 25,000 5833.33 7500 1750 115,308.33 75,225.00 25,000 5833.33 7500 1750 115,308.33 75,225.00 25,000 5833.33 7500 1750 115,308.33 72,275.00 25,000 5833.33 7500 1750 112,358.33 72,275.00 25,000 5833.33 7500 1750 112,358.33 66,375.00 25,000 5833.33 7500 1750 106,458.33 66,375.00 25,000 5833.33 7500 1750 106,458.33 63,425.00 25,000 5833.33 7500 1750 103,508.33 63,425.00 25,000 5833.33 7500 1750 103,508.33 63,425.00 25,000 5833.33 7500 1750 103,508.33 66,375.00 25,000 5833.33 7500 1750 106,458.33 $ 837,800.00 $ 300,000.00 $ 69,999.96 $ 90,000.00 $ 21,000.00 $ 1,318,799.96 The following information has been gathered about the practice: ENT Clinic, Miles Private Hospital Balance Sheet As at 31 December 2020 Assets Cash Accounts Receivable ENT Equipment Less: Accumulated Depreciation Total Assets SAUD 56,500 48,895 1,560,000 (190,000) $1,475,395 Liabilities & Shareholder Equity Accounts Payable Bank Loan Retained Earnings Total Liabilities & Shareholders' Equity 56,400 600,000 818,995 $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 specialists 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 John Malouf 95 95 95 95 95 January 2021 February 2021 March 2021 April 2021 May 2021 June 2021 July 2021 August 2021 September 2021 October 2021 November 2021 December 2021 January 2022 February 2022 Dr Janet Courtney Dr Samantha Downer 90 80 80 80 80 80 80 80 70 80 70 80 50 80 50 80 40 80 40 80 40 80 50 80 40 80 40 80 95 95 95 95 95 95 95 95 95 The 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 (60%) of the revenue received for each month's ENT's services 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 costs for the year. Rent Utilities (Water & Electricity) Insurance Telephone, Internet & IT support General Office Expenses Surgical supplies Repairs & maintenance of ENT equipment Operating Costs $1 000 /month $120 / month $10 000/year $200 / month $200 /month $2600/month $700 / quarter Payment Paid monthly Paid quarterly Paid monthly Paid monthly Paid monthly Paid quarterly Paid March, June, September & December Paid monthly Each ENT Specialist's Individual Salary $300 000/year excluding on-costs Each Administrative Assistants Salary $70 000/year excluding on-costs Paid monthly Payments: It is estimated that 30% of all operating expenses will be paid in the month they are due and the remaining 70% will be 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-costs are 30% of their annual salary. They work 37.5 hours per week. Each employee's allowance for annual leave, public holidays and sick leave amounts to 8 weeks per year. They are also given an allowance for non-billable time of 2 hours per week which includes professional development, department meetings and idle time. Ignore GST and Income Tax. ASSIGNMENT REQUIREMENTS: PART ONE: Budget Schedules & Expected Outcomes for 2021 (15 marks/30 marks, weighted 50% of assignment total) Using the information provided, prepare the following operating and financial budgets for the twelve-month period from January 2021 to December 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 2021 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 WORD document). The first worksheet should contain your raw/input data and assumptions from this assignment overview sheet. All future worksheets should be linked to this data. Show all calculations and use Excel functions where possible. While 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 difficulty. PART TWO: Analysis & Report to the Specialists of the ENT Clinic (15 marks/30 marks, 50% of assignment total) Write a report (Maximum 1,500 words, 10% (+/-) will be accepted with no penalty) for the three specialists at ENT Clinic. The report should include: An overview of the expected results for the ENT clinic for the 2021 year including total revenue, operating expenses and expected cash position; a cost-volume-profit (CVP) analysis to indicate the breakeven point (in patients and revenue dollars) and provide recommendations regarding cost control strategies and how to improve the current margin of safety, an analysis of the business operations (i.e. current incomes and expenses) and recommendations regarding how to improve the profitability of the ENT Clinic. Use industry data (e.g. market rate for salaries) to support any recommendations for changes. Explain how the budgeting process can assist the ENT specialists to articulate decisions about the use of resources and how the budget can be used throughout the year to analyse variances and lead to continuous improvement within the clinic. Use visual aids such as tables and graphs to enhance your report presentation. REVENUE BUDGET Half hour One hour Dr Downer appointments surgeries 80 60 80 60 80 60 80 60 Half hour One hour appointments surgeries @ Monthly @ $260 $400 revenue 90 67.5 22.5 26,550.00 80 60 20 23,600.00 80 60 20 23,600.00 80 60 20 23,600.00 70 52.5 17.5 20,650.00 70 52.5 17.5 20,650.00 50 37.5 12.5 14,750.00 50 37.5 12.5 14,750.00 40 30 10 11,800.00 40 30 10 11,800.00 40 30 10 11,800.00 50 37.5 12.5 14,750.00 80 60 2021 Dr Courtney January February March April May June July August September October November December Total revenue per physician Monthly revenue Dr Malouf 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 20 23,600.00 Half hour Total appoint one hour Monthly monthly ments surgeries revenue revenue 95 71.25 23.75 28,025.00 78,175.00 95 71.25 23.75 28,025.00 75,225.00 95 71.25 23.75 28,025.00 75,225.00 95 71.25 23.75 28,025.00 75,225.00 95 71.25 23.75 28,025.00 72,275.00 95 71.25 23.75 28,025.00 72,275.00 95 71.25 23.75 28,025.00 66,375.00 95 71.25 23.75 28,025.00 66,375.00 95 71.25 23.75 28,025.00 63,425.00 95 71.25 23.75 28,025.00 63,425.00 95 71.25 23.75 28,025.00 63,425.00 95 71.25 23.75 28,025.00 66,375.00 80 60 80 60 60 60 80 80 80 80 60 60 80 60 $218,300.00 $ 283,200.00 $336,300.00 $837,800.00 MONTHLY OPERATING EXPENSE BUDGET Rent 1,000.00 Utilities 120.00 Insurance 833.33 Telephone, Internet, & IT support 200.00 General office expenses 200.00 Surgical supplies 2,600.00 Repairs & maintenance of ENT equipment 233.33 Total monthly operating expense $ 5,186.66 January February March April May June July August September October November December Total LABOUR BUDGET Labor cost of appointment Specialist Assistant On-costs of On-costs of (specialist) salary salary specialist assistants Total 78,175.00 25,000 5833.33 7500 1750 118,258.33 75,225.00 25,000 5833.33 7500 1750 115,308.33 75,225.00 25,000 5833.33 7500 1750 115,308.33 75,225.00 25,000 5833.33 7500 1750 115,308.33 72,275.00 25,000 5833.33 7500 1750 112,358.33 72,275.00 25,000 5833.33 7500 1750 112,358.33 66,375.00 25,000 5833.33 7500 1750 106,458.33 66,375.00 25,000 5833.33 7500 1750 106,458.33 63,425.00 25,000 5833.33 7500 1750 103,508.33 63,425.00 25,000 5833.33 7500 1750 103,508.33 63,425.00 25,000 5833.33 7500 1750 103,508.33 66,375.00 25,000 5833.33 7500 1750 106,458.33 $ 837,800.00 $ 300,000.00 $ 69,999.96 $ 90,000.00 $ 21,000.00 $ 1,318,799.96Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started