+1443 776-2705 panelessays@gmail.com

 

Download the Lesson 5 Budget SpreadsheetPreview the document (Excel) and respond to the following questions in a Word document. Be sure to show all of your work.

  1. After reviewing the budget, including the Budget Assumptions on the Instructions tab, develop two Operating Plan Objectives for the HIMT department.
  2. Looking at the Stats and Revenue tab, which trend in budget variance will need to be monitored closely in 2019, based on the 2018 data?
  3. Describe the change in “Total paid hours: staff” from 2017 to 2018 found on the Expense tab. What are some possible explanations for the change?
  4. Describe the change in data processing software expenses from 2016 to 2017, found on the Expenses tab. What is the most likely reason for this increase?
  5. Turn to the Department History tab. Why are the numbers on the line “Excess of Revenue over Expenses” in parentheses?
  6. You have permission to hire an additional Documentation Integrity Specialist. The person you are interested in is experienced. The hospital policy is that you may not pay them any more than the average or mean amount being paid to any employee in that job category. What is the maximum amount of hourly pay that you can offer the new hire?
  7. Is the supply budget variance or the salary budget variance, located on the Summary page, more concerning to you? Explain your rationale.
  8. Locate the Summary tab. What is the average hourly wage for staff on the projected budget for 2018? What is the amount budgeted as the average hourly wage for 2019? What is the most likely cause of the change?
  9. What is one thing that surprised you about reviewing the budget?
  10. Have any gaps in your knowledge of budgeting become apparent after doing this review? If so, how will you fill those gaps?

Instructions

BUDGET2019_Assumptions.TabLink BUDGET2019_Assumptions.Description BUDGET2019_Assumptions.Amount
[View;Default] [HideColumn] [HideColumn] [HideColumn] [HideColumn]
BUDGET2019_Assumptions.Description
Instructions
Health Information Management Budget_V2
Timeline and Deadlines for submitting budgets: Due
[aq1] Prefix code >>
1 Instructionline1 0 Instructionline 1 Distribution of Budget Templates 2/9/
2 Instructionline2 0 Instructionline 2 Deadline for Completion and Director Approval – Non Revenue 2/28/
3 Instructionline3 0 Instructionline 3 Deadline for Completion and Director Approval – Revenue 3/9/
4 Instructionline4 0 Instructionline 4 Vice President / Fiscal Budget Reviews – As-needed 3/19 – 3/30/
5 Instructionline5 0 Instructionline 5 Date FY 2019 Budget Presented to Board 4/27/
6 Instructionline6 0 Instructionline 6
7 Instructionline7 0 Instructionline 7
8 Instructionline8 0 Instructionline 8
[aq2] Your contact for Budgeting Questions is:
1 Contact1 0 Contact UWHC [email protected] UWHC [email protected] UWHC [email protected]
2 Contact2 0 Contact
3 Contact3 0 Contact
[stop] Budget Assumptions Change
1 Item1 0 Item 1 FY18 Projected to FY19 Budget: 0.0%
2 Item2 0 Item 2 Change in Patient Days 2.1%
3 Item3 0 Item 3 Change in Clinic Visits 0.4%
4 Item4 0 Item 4 Change in Admissions 2.1%
5 Item5 0 Item 5 Change in Surgeries 2.3%
6 Item6 0 Item 6 Total Acquisition Cost from FY18 Projected to FY19 Budget: 0.0%
7 Item7 0 Item 7 Change in Patient Days 1.0%
8 Item8 0 Item 8 Change in Clinic Visits 1.2%
9 Item9 0 Item 9 Change in Admissions 26.7%
10 Item10 0 Item 10 Change in Surgeries 3.7%
11 Item11 0 Item 11 All Inflation assumptions will be provided by Finance 0.0%
12 Item12 0 Item 12 All rate changes will be provided by Finance 0.0%
[stop]
Instructions for Budgeting:
1) Obtain a copy of the instructions from Budget Administration and read before you begin.
2) Budget Plan File Legend
History or calculation 12,345
Input Area 12,345
Drop-Down Selection Admissions
3) Modify worksheets as Follows:
a STATISTICS: Adjust the Current Year Projection & Next Years Budget Accordingly
b REVENUE: Adjust the Current Year Projection & Next Years Budget Accordingly
c EMPLOYEE: Modify Employee worksheet according to instructions
d EMPLOYEE LISTING: (Information only) – Displays currently assigned employees
e EXPENSE: Adjust the Current Year Projection & Next Years Budget Accordingly
f HISTORY: (Information only) – Displays historical monthly account activity

Plan

[AX;A;635852173192018364] [AX;B;635852173192018364] [AX;C;635852173192018364] [AX;D;635852173192018364] [AX;E;635852173192018364] [AX;F;635852173192018364] [AX;G;635852173192018364] [AX;H;635852173192018364] [AX;I;635852173192018364] [AX;J;635852173192018364] [AX;K;635852173192018364] [AX;L;635852173192018364] [AX;M;635852173192018364] [AX;N;635852173192018364] [AX;O;635852173192018364] [AX;P;635852173192018364] [AX;Q;635852173192018364] [AX;R;635852173192018364] [AX;S;635852173192018364] [AX;T;635852173192018364] [AX;U;635852173192018364] [AX;V;635852173192018364] [AX;W;635852173192018364] [AX;X;635852173192018364] [AX;Y;635852173192018364] [AX;Z;635852173192018364] [AX;AA;635852173192018364] [AX;AB;635852173192018364] [AX;AC;635852173192018364] [AX;AD;635852173192018364] [AX;AE;635852173192018364] [AX;AF;635852173192018364] [AX;AG;635960766475862366;CM]
[GoToColumn;Plan]
BUDGET2019_Assumptions.Description
[view;Default] [hidecolumn]
ACCT DEPT INITIATIVEID [Save2DB;BUD2019_Detail;;;;;FALSE] RECORDID PlanQuestion
[view;All] [InsertCMColumn;] LineRef [HideColumn]
BudgetGroup >> UWHC 800000 44040
Prefix code >>
[GoTo;Operating Plan] T1 Operating Plan
Title T2 [aq1] Health Information Management
T3
[GoTo;Top] T4
1 1 T5 Plan1 Objectives
1 800000 44040 1 Do Not Save T6 101
1 800000 44040 1 Do Not Save T7 102
1 800000 44040 1 Do Not Save T8 103
1 800000 44040 1 Do Not Save T9 104
1 800000 44040 1 Do Not Save T10 105
[InsertCM;;Add New Line;;insert;above;0;] T11 Double Click to Insert New Planning Lines
2 2 T12 Plan2 Risk Factors
2 800000 44040 1 Do Not Save T13 201
2 800000 44040 1 Do Not Save T14 202
2 800000 44040 1 Do Not Save T15 203
2 800000 44040 1 Do Not Save T16 204
2 800000 44040 1 Do Not Save T17 205
[InsertCM;;Add New Line;;insert;above;0;] T18 Double Click to Insert New Planning Lines
3 3 T19 Plan3 Factors That May Aid In Accomplishing The Objectives
3 800000 44040 1 Do Not Save T20 301
3 800000 44040 1 Do Not Save T21 302
3 800000 44040 1 Do Not Save T22 303
3 800000 44040 1 Do Not Save T23 304
3 800000 44040 1 Do Not Save T24 305
[InsertCM;;Add New Line;;insert;above;0;] T25 Double Click to Insert New Planning Lines
4 4 T26 Plan4 Provide Any Operational Factors That Will Not Occur Next Year
4 800000 44040 1 Do Not Save T27 401
4 800000 44040 1 Do Not Save T28 402
4 800000 44040 1 Do Not Save T29 403
4 800000 44040 1 Do Not Save T30 404
4 800000 44040 1 Do Not Save T31 405
[InsertCM;;Add New Line;;insert;above;0;] T32 Double Click to Insert New Planning Lines
5 5 T33 Plan5 Provide Any New Operational Factors That May Occur Next Year
5 800000 44040 1 Do Not Save T34 501
5 800000 44040 1 Do Not Save T35 502
5 800000 44040 1 Do Not Save T36 503
5 800000 44040 1 Do Not Save T37 504
5 800000 44040 1 Do Not Save T38 505
[InsertCM;;Add New Line;;insert;above;0;] T39 Double Click to Insert New Planning Lines
6 6 T40 Plan6 Quality Improvement Plan
6 800000 44040 1 Do Not Save T41 601
6 800000 44040 1 Do Not Save T42 602
6 800000 44040 1 Do Not Save T43 603
6 800000 44040 1 Do Not Save T44 604
6 800000 44040 1 Do Not Save T45 605
[InsertCM;;Add New Line;;insert;above;0;] T46 Double Click to Insert New Planning Lines
7 7 T47 Plan7 Labor Productivity Plan
7 800000 44040 1 Do Not Save T48 701
7 800000 44040 1 Do Not Save T49 702
7 800000 44040 1 Do Not Save T50 703
7 800000 44040 1 Do Not Save T51 704
7 800000 44040 1 Do Not Save T52 705
[InsertCM;;Add New Line;;insert;above;0;] T53 Double Click to Insert New Planning Lines
8 8 T54 Plan8 Other Expense Containment Plan
8 800000 44040 1 Do Not Save T55 801
8 800000 44040 1 Do Not Save T56 802
8 800000 44040 1 Do Not Save T57 803
8 800000 44040 1 Do Not Save T58 804
8 800000 44040 1 Do Not Save T59 805
[InsertCM;;Add New Line;;insert;above;0;] T60 Double Click to Insert New Planning Lines
9 9 T61 Plan9 Anticipated Patient / Acuity Change
9 800000 44040 1 Do Not Save T62 901
9 800000 44040 1 Do Not Save T63 902
9 800000 44040 1 Do Not Save T64 903
9 800000 44040 1 Do Not Save T65 904
9 800000 44040 1 Do Not Save T66 905
[InsertCM;;Add New Line;;insert;above;0;] T67 Double Click to Insert New Planning Lines
[HideRow] 10 10 T68 Plan10
[HideRow] 10 800000 44040 1 Do Not Save T69 1001
[HideRow] 10 800000 44040 1 Do Not Save T70 1002
[HideRow] 10 800000 44040 1 Do Not Save T71 1003
[HideRow] 10 800000 44040 1 Do Not Save T72 1004
[HideRow] 10 800000 44040 1 Do Not Save T73 1005
[HideRow] [InsertCM;;Add New Line;;insert;above;0;] T74 [stop] Double Click to Insert New Planning Lines

Stat_Rev

[AX;A;635852188895600997] [AX;B;635852188895600997] [AX;C;635852188895600997] [AX;D;635852188895600997] [AX;E;635852188895600997] [AX;F;635852188895600997] [AX;G;635852188895600997] [AX;H;635852188895600997] [AX;I;635852188895600997] [AX;J;635852188895600997] [AX;K;635852188895600997] [AX;L;635852188895600997] [AX;M;635852188895600997] [AX;N;635852188895600997] [AX;O;635852188895600997] [AX;P;635852188895600997] [AX;Q;635852188895600997] [AX;R;635852188895600997] [AX;S;635852188895600997] [AX;T;635852188895600997] [AX;U;635852188895600997] [AX;V;635852188895600997] [AX;W;635852188895600997] [AX;X;635852188895600997] [AX;Y;635852188895600997] [AX;Z;635852188895600997] [AX;AA;635852188895600997] [AX;AB;635852188895600997] [AX;AC;635852188895600997] [AX;AD;635852188895600997] [AX;AE;635852188895600997] [AX;AF;635852188895600997] [AX;AG;635852188895600997] [AX;AH;635852188895600997] [AX;AI;635852188895600997] [AX;AJ;635852188895600997] [AX;AK;635852188895600997] [AX;AL;635852188895600997] [AX;AM;635852188895600997] [AX;AN;635852188895600997] [AX;AO;635852188895600997] [AX;AP;635852188895600997] [AX;AQ;635852188895600997] [AX;AR;635852188895600997] [AX;AS;635852188895600997] [AX;AT;635852188895600997] [AX;AU;635852188895600997] [AX;AV;635852188895600997] [AX;AW;635852188895600997] [AX;AX;635852188895600997] [AX;AY;635852188895600997] [AX;AZ;635852188895600997] [AX;BA;635852188895600997] [AX;BB;635852188895600997] [AX;BC;635852188895600997] [AX;BD;635852188895600997] [AX;BE;635852188895600997] [AX;BF;635852188895600997] [AX;BG;635852188895600997] [AX;BH;635852188895600997] [AX;BI;635852188895600997] [AX;BJ;635852188895600997] [AX;BK;635852188895600997] [AX;BL;635852188895600997] [AX;BM;635852188895600997] [AX;BN;635852188895600997] [AX;BO;635852188895600997] [AX;BP;635852188895600997] [AX;BQ;635852188895600997] [AX;BR;635852188895600997] [AX;BS;635852188895600997] [AX;BT;635852188895600997] [AX;BU;635852188895600997] [AX;BV;635852188895600997] [AX;BW;635852188895600997] [AX;BX;635852188895600997] [AX;BY;635852188895600997] [AX;BZ;635852188895600997] [AX;CA;635852188895600997] [AX;CB;635852188895600997] [AX;CC;635852188895600997] [AX;CD;635852188895600997] [AX;CE;635852188895600997] [AX;CF;635852188895600997] [AX;CG;635852188895600997] [AX;CH;635852188895600997] [AX;CI;635852188895600997] [AX;CJ;635852188895600997] [AX;CK;635852188895600997] [AX;CL;635852188895600997] [AX;CM;635852188895600997] [AX;CN;635852188895600997] [AX;CO;635852188895600997] [AX;CP;635852188895600997] [AX;CQ;635852188895600997] [AX;CR;635852188895600997] [AX;CS;635852188895600997] [AX;CT;635852188895600997] [AX;CU;635852188895600997] [AX;CV;635852188895600997] [AX;CW;635852188895600997] [AX;CX;635852188895600997] [AX;CY;635852188895600997] [AX;CZ;635852188895600997;CM] [AX;DA;635852188895600997]
[RowView]
Description L2A LYA CYB YTD CYPCalc CYPTtl NYBTtl NYB1 NYB2 NYB3 NYB4 NYB5 NYB6 NYB7 NYB8 NYB9 NYB10 NYB11 NYB12 NYBSum
CYPRate GlbPct NYBPct NYBAmt NYBRate NYBFlag NYB NYBComments NYBMethod
Print Options [Print;Annual View;Annual View;Landscape;69:75;D:D;1;;;;;;;;Letter] [Print;Projection View;Projection View] [Print;Monthly View;Monthly View]
ACCT RECORDID DEPT RECORDIDDesc BudTtl P1 P2 P3 P4 P5 P6 P7 P8 P9 P10 P11 P12 BudSum BudComments INITIATIVEID
Acct DEPT BudLYATOT BudCYBTOT CYAYTDVerification ProjCalc ProjAdj ProjTtl BudPrelim BudPct BudAmt BudTtl BudFlag P1 P2 P3 P4 P5 P6 P7 P8 P9 P10 P11 P12 BudSum BudComments BudMethod [Save2DB;BUD2019;;;;;FALSE] INITIATIVEID KeyStat
Description L2A LYA CYB YTD CYPCalc CYPAdj CYPTtl NYBPrelim NYBTtl HA_Target NYB1 NYB2 NYB3 NYB4 NYB5 NYB6 NYB7 NYB8 NYB9 NYB10 NYB11 NYB12 NYBSum
ACCT.KHABgtCode DEPT.KHABgtMap ACCT.KHABgtCode.Description ACT2016.TOT ACT2017.TOT BUD2018.TOT ACT2018.YTD6 HA_Target BUD2018.YTD6 ACT2017.YTD6 ACT2018.NYBKHA INITIATIVEID KeyStat
ACCT.KHABgtCode DEPT.KHABgtMap INITIATIVEID
ACCT.KHABgtCode DEPT.KHABgtMap
0 0
DEPT.KHABgtMap INITIATIVEID CYF2018.YTD6 CYF2018.TOT
[SetColumn;15] [HideColumn] [SetColumn;15] Payor Adj View [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn][View;Payor Adjustment] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn]
[SetColumn;15] [HideColumn] [SetColumn;15] Annual View [HideColumn] 0 0 [HideColumn] [HideColumn] [HideColumn] 0 [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn][View;Annual View] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn]
[SetColumn;15] [HideColumn] [SetColumn;15] Monthly View [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn][View;Monthly View] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn]
[SetColumn;15] [HideColumn] [SetColumn;15] Projection View [HideColumn] [HideColumn] [HideColumn] 0 0 0 [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn][View;Projection View] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn]
[SetColumn;15] [HideColumn] [SetColumn;15] All [HideColumn] 0 0 0 0 0 [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [View;ALL] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn] [HideColumn]
[aq4] [HideColumn] [SetColumn;15] Comments