Budget Projections

Paper details:

Module 4

For this activity, you will modify an existing budget to increase revenue.

This activity requires the use of Microsoft Excel. The following materials may be useful as you begin this activity:

Atomic Learning Software Tutorials: Microsoft Excel 2010

Microsoft Excel Tutorial for Beginners #1 – Overview (YouTube)

In this case scenario, you are the nurse administrator for an outpatient podiatry clinic. You have been informed by the CNO that the clinic needs to increase revenue by 10% for the next fiscal year or they will need to cut expenses by laying-off personnel.

Utilize the linked excel file of the revenue budget, make the appropriate changes in excel to project a 10% revenue increase for next year.

Excel File

You can project an increase or decrease in any line item except ‘Gift Shop” and “Donations”. You are to assume that there will be no donations next year and gift shop donations will be reduced to $5500 due to a planned renovation of the shop that will require closure for 2 months.

Please begin the conversation by posting an analysis of your proposed budget and rationale for the changes you plan to make.

By the end of the discussion period, post your final response, and upload your Excel spread sheet as an attachment to your discussion post for review and comments from your instructor. Students have the option to review and comment on each other’s submissions.

Posts may include your experiences, opinions, view points, and ideas supported by evidence (references) as appropriate.

(A) (B) (C=AxB) D E=(CxD)

Revenue Source Quantity Rate or Charge Gross Revenue Average Net Charge Revenue Net of Discounts and Allowances
Same Day Surgery
Private Insurance 1,000 1,500 1,500,000 75% 1,125,000
Medicare 600 1,500 900,000 80% 720,000
Medicaid 400 1,500 600,000 60% 360,000
Self-Pay 500 1,500 750,000 70% 525,000
Clinic Patients
Private Insurance 3,000 250 750,000 75% 562,500
Medicare 1,800 250 450,000 80% 360,000
Medicaid 1,200 250 300,000 60% 180,000
Self-Pay 1,500 250 375,000 85% 318,750
Gift Shop 7,000 17 119,000 99% 117,810
Donations 400 200 80,000 100% 80,000
Subtotal 5,824,000 4,349,060
Less Bad Debt Expense -112,500
Net Revenue Less Bad Debt Expense 4,236,560

Note: Adopted from: “Financial Management for Nurse Managers and Executives,” by S.A. Finkler, C. T. Kovner, and C. B. Jones, 2007, Copyright by Saunders