I need some help with this please, the excel spreadsheet is copied at the bottom. For the excel , could you let me know which formulae to use please? And show me the work for the calculations please?

Case Study #2

It is October 2019. You are the new office manager of The GetWell Family Practice Clinic. The two owners of the clinic have directed you to develop a clinic operating budget fo0r the next year. You are now in the process of developing a pro forma (just a fancy term meaning projected) budget statement for January-December 2020.You looked at the last two months of clinic data, extrapolated for 12 months and came up with the following information:

For projected revenues:

# of Clinic Visits Insurance SourceAvg Revenue per visit

10,000Private Insurance$60

6,000Medicare$40

1,000Medicaid$30

500Uninsured/Self-pay$125

For projected expenses:

Salaries and Benefits

Physicians$380,000

Nurses$156,000

Medical Assistants$78,000

Admin Support(You)$75,000

Medical Supplies

$8 per clinic visit

Administrative Supplies$8,000

Rent and Utilities$52,000

1.Create a Pro Forma Budget using the provided spreadsheet. Fill in highlighted cells in Column B.

During 2020, the clinic owners were too busy to ask about how things were going financially.Now it is January 2021 and the clinic owners want to know what the financial performance of the clinic was for 2020.You gather all the information you need and compile the following:

Actual # of Clinic VisitsInsurance SourceAvg Revenue per visit

8,953Private Insurance$58

6,426Medicare$39

1,965Medicaid$30

542Uninsured/Self-pay$60 (Figure already adjusted for people who did not pay)

For actual expenses:

Salaries and Benefits

Physicians$379,110

Nurses$165,396

Medical Assistants$75,114

Admin Support$75,533

Medical Supplies$152,420

Administrative Supplies$7,009

Rent and Utilities$55,620

2.Fill in the Actual Budget Results in the provided spreadsheet.Fill in highlighted cells in Column D.

3.On the same spreadsheet, calculate the variances (actual dollars and percentages) for the various categories of revenues and expenses. Fill in highlighted cells in Columns F and G.

Answer the following questions

1.Summarize the overall performance of the clinic to include:

a.When comparing the projected performance with the actual financial performance, are the owners happy with the clinic's financial performance?

b.Which had a bigger impact on the actual overall financial performance of the clinic? Revenues or expenses?

c.Which specific 2 budget items showed the biggest $$ variances?Comment on what you believe were some possible causes for these variances.

d.Explain how you can use at % variances information?

2.What two changes to the budget process you followed would you make to improve the budget management of the clinic?

3.Provide two recommendations to the owners to try to improve the financial performance of the clinic.

EXCEL SPREADSHEET INFO

January- December 2020 Pro Forma Budget Get Well Family Practice Clinic

Year 2020

Revenues_______ Private Insurance__________ Medicare_________Medicaid __________ Self-pay _______ Total revenues____________

Expenses __________Salaries and Benefits_________ Medical Supplies________ Administrative Supplies___________Rent and Utilities___________

Total expenses________

Income/Loss__________

Note: $ Variance is dollar figure such as $125 or -$125. Note: % Variance is a percentage such as 10% or -10%

Pro Forma Actual Results Year 2020 End of 2020 $ Variance % Variance