BUDGET Genesis Pajarito Community Hospital Health Information Management Department Fiscal Year 2021 Personal Expenses JAN Thomas Weatherby Mai Cheng Anna Fernandez Jefferson Taylor Elizabeth Adams New Employee Vacation Relief FEB 4290 3600 2850 2600 1820 4290 3600 2850 2600 1820 MAR 4290 3780 2850 2600 1820 2233 APR 4290 3780 2850 2600 1820 2233 MAY 4290 3780 2850 2600 1820 2233 JUN 4290 3780 2850 2720 1820 2233 Total Salaries Payroll Taxes Fringe Benefits Total Personnel Other Expenses Photocopying Travel Materials and Supplies Machine Maintenance Telephone Total Other Direct Costs Total expenses Total operating Budget JULY 4290 3780 2850 2720 1820 2233 AUG 4490 3780 2850 2720 1820 2233 SEPT 4490 3780 2850 2720 1820 2353 PCT 4490 3780 2990 2720 1820 2353 NOV 4490 3780 2990 2720 1900 2353 DEC 4490 3780 2990 2720 1900 2353 TOTAL Lesson - Department Budget Variance Analysis Instructions: Review the following information

Subject:BusinessPrice:16.89 Bought3

Share With

BUDGET Genesis Pajarito Community Hospital Health Information Management Department Fiscal Year 2021 Personal Expenses JAN Thomas Weatherby Mai Cheng Anna Fernandez Jefferson Taylor Elizabeth Adams New Employee Vacation Relief FEB 4290 3600 2850 2600 1820 4290 3600 2850 2600 1820 MAR 4290 3780 2850 2600 1820 2233 APR 4290 3780 2850 2600 1820 2233 MAY 4290 3780 2850 2600 1820 2233 JUN 4290 3780 2850 2720 1820 2233 Total Salaries Payroll Taxes Fringe Benefits Total Personnel Other Expenses Photocopying Travel Materials and Supplies Machine Maintenance Telephone Total Other Direct Costs Total expenses Total operating Budget JULY 4290 3780 2850 2720 1820 2233 AUG 4490 3780 2850 2720 1820 2233 SEPT 4490 3780 2850 2720 1820 2353 PCT 4490 3780 2990 2720 1820 2353 NOV 4490 3780 2990 2720 1900 2353 DEC 4490 3780 2990 2720 1900 2353 TOTAL Lesson - Department Budget Variance Analysis Instructions: Review the following information. You may want to print the lesson out so you have it available to study for the exam. MONITORING THE EXPENSES AGAINST THE BUDGET A budget should be a live, working plan. For it to serve in this capacity a budget requires timely and accurate reporting of operating results. The actual results of HIM department operations are reported monthly, quarterly and annually from the Accounting Department staff. These are compared with your budgeted amounts. As a supervisor, you are required to review the results very carefully and respond to your supervisor if any items are over or under budget – usually by a certain percent. Sometimes there are expected variations but sometimes the expenses over budget are not expected. Administration requires you to keep your expenses under control and if you are over too much, for too long, without authorization for the increased expenses, you most likely will be required to cut someplace else in your budget later during the year in order to break even by year’s end. Let us see how this works. They compare actual expenses (which are often called “actuals”) with the budgeted amount. Here is an example: Item 1st Quarter Actuals 1st Quarter Budgeted Supplies $36,457 $32,800 Telephone $953 $1,150 Just by looking at these we can see that we spent too much for supplies but we were under our budget in telephone expenses. We often report not only amount of money over or under budget but also provide a percent of what that means. When you are dealing on the expense side, over 100% of budget is not a good thing – you want to be 100% of budget or below 100%. For those departments that worry about revenue (which HIM does not), then you want to be 100% of budget or higher than 100%. 100% means you spent the exact budgeted amount. If you are below 100% that is not good, because that means you are bringing in less money to the institution. Fortunately, the HIM department only worries about the expense side, so that is where we will focus our discussion using the above example. SUPPLIES $36,457 - $32,800 = $3,657 above budget in supplies There are two ways to convert it to a percent Method One: (actuals x 100)/budgeted (36,457 x 100)/32,800 = 111.15% of budget or 11.15% over budget Method Two: actuals – budget = net $36,457 – $32,800 = $3,657 (net x 100)/budgeted ($3,657x 100)/$32,800 = 11.15% over budget TELEPHONE Method One: ($953 x 100) – $1,150 = 82.9% of budget (100% - 82.9% =17.1% under budget) Method Two: $1,150 - $953 = $197 under budget or ($197 x 100)/$1,150=17.1% under budget Let’s do a table together: INITIAL BUDGET Outpatients Actual Budget Visits 16,500 13,500 Salaries $460,000 $360,000 Supplies $75,000 $55,000 Variance (Actual – Budget) % of budget % +/budget Visits: To calculate variance we take actual minus the budget. 16,500 – 13,500 = 3,000 number of visits over budget. To find the percent of budget we take (16,500 x 100)/13,500 = 122.2%. We were 22.2% over budget in terms of number of visits. Do the same for salaries and supplies and compare it with the following table. Outpatients Actual Budget Variance (Actual – Budget) % of budget % +/budget Visits 16,500 13,500 +3,000 122.2% +22.2% Salaries $460,000 $360,000 +$100,000 127.8% +27.8% Supplies $75,000 $55,000 +$20,000 136.4 +36.4% PRACTICE Fill in the squares and then look at the answers below. Variance 1st Qtr Actual 1st Qtr Budget Supplies 15,345 14,000 Travel 1,200 1,200 Cont Ed 3,040 2,500 Telephone 894 1,000 2nd Qtr 2nd Qtr Variance Actual Budget (Actual – Budget) Supplies 13,200 14,000 Travel 5,000 5,000 Cont Ed 1,500 2,500 Telephone 1,020 1,000 (Actual – Budget) % of budget TOTAL % of budget TOTAL Variance Total Actual Total Budget (Actual – Budget) % of budget % +/1 budget TOTAL 2 quarters ANSWERS 1st Qtr Actual 1st Qtr Budget Variance Supplies 15,345 14,000 $1,345 over 9.6% over (or 109.6 % of budget) Travel 1,200 1,200 $0 Right on budget Cont Ed 3,040 2,500 $540 over 21.6% over (or 121.6% of budget) Telephone 894 1,000 $106 under 10.6% under (or 89.4% of budget) TOTAL $20,479 $18,700 $1,1779 over 9.5% over (or 109% of budget) 2nd Qtr Actual 2nd Qtr Budget Variance Supplies 13,200 14,000 $800 under 5.7% under (or 94.3% of budget) Travel 5,000 5,000 $0 Right on budget Cont Ed 1,500 2,500 $1,000 under 40% under (or 60% of budget) (Actual – Budget) (Actual – Budget) % of budget % of budget Telephone 1,020 1,000 $20 over 2% over (or 102% of budget) TOTAL $20,720 $22,500 $1,780 under 7.9% under (or 92.1% of budget) Total Actual Total Budget Variance $41,199 $41,200 $1 (Actual – Budget) % of budget TOTAL Less than 1% 2 quarters VARIANCE ANALYSIS WITH AN ADJUSTMENT FOR VOLUME Sometimes we go over budget because we have more to do in our department. For example, in an outpatient clinic, if the clinic has many more patient visits than they had originally anticipated and budgeted for, then naturally the HIM department has more work to do and has to hire additional staff and use more supplies than originally budgeted. In order to make this fair, most facilities will figure out how much over budget the number of visits there were. Let’s say that there were 20% more visits than had been planned. Then, your salaries and supplies costs should also be 20% more. Therefore, many places will recalculate your budget accordingly and call it an ADJUSTED BUDGET. As an example, let's say that your clinic had 25% more visits than originally budgeted. Your original budget for HIM salaries was $100,000. If we take $100,000 x 0.25 we will get $25,000. So, that’s how much more it will cost your HIM department to do its work for the additional patients. So, they will now say that your new budget for salaries (or your adjusted budget) is now $125,000 (old budget plus the additional costs associated with the additional patients). Using this same example, if your supplies budget was $20,000, you should be able to add an additional 25% to that to handle the additional patients. So, $20,000 x 0.25 = $5,000. So your new, adjusted supplies budget would be $20,000 + $5,000 or $25,000. These new adjusted budgets are now more fair to your department. Let’s say that you spent $145,000 for salaries and $22,000 for supplies (actual). Are you over or under your adjusted budget in each of these two categories? Let’s do the math. Salaries: Actuals x 100 Budget $145,000 x 100 $125,000 = 116% - or you were 16% over budget Adj. Actuals x 100 $22,000 x 100 = 88% - or you were 12% under budget Adj. Budget $25,000 Supplies: Although salaries are still over the adjusted budget, 16% over budget is much better than if they had not given you an adjusted budget. Let’s say you were told you had to keep the original $100,000 budget for salaries. Then your analysis would show: $145,000 x 100 / $100, 000= 145% or 45% over budget!!!! Lets look at the first table we calculated previously: Outpatients Actual Budget Variance (Actual – Budget) % of budget % +/budget Salaries $460,000 $360,000 +$100,000 127.8% +27.8% Supplies $75,000 $55,000 +$20,000 136.4 +36.4% Upon looking at the number of visits, we discover that there were 22.2% more visits that were actually made compared with the budgeted amount due to the unanticipated closure of another clinic in the same area of the city near our medical center and their patients are now coming to our facility for care. Our budget is now adjusted to be 22.2% higher for expenses. For salaries we take our budget of $360,000 and calculate 22.2% more money allowed to us. So $360,000 x 1.222 = $439,920. Or, you can calculate the same number by taking $360,000 x 0.222 = $79,920 and then add $360,000 (original budget) plus $79,920 (22.2% more allowed to us) = $439,920= Adjusted budget. For supplies we take our budget of $55,000 and calculate 22.2% more money allowed to us. So $55,000 x 1.222 = $67,210. So, taking these calculations, let’s convert them to a table to make them easier to review. ADJUSTED BUDGET % of Variance (Act Outpatien Adjustment Amo Adjusted Bud adj. Actual Budget ual - adj ts unt get budge budget) t % +/adj budge t Salaries $460,00 $360,00 $79,920 0 0 $439,920 $20,080 104.6 +4.6% % Supplies $75,000 $55,000 $12,210 $67,210 $7,790 111.6 +11.6 % % 1 ASSIGNMENT #5 HEALTH INFORMATION DEPARTMENT BUDGET INTRODUCTION The departmental budget is a basic management tool, both in planning and controlling. It is through analysis of the previous budget and evaluation of current and future needs that accurate projections can be made. The purpose of this project is to have you become acquainted with the principles of departmental budgeting, even though each facility will have its own forms and level of detail. Since Health Information Specialists often become supervisors and occasionally department managers, it is important for the specialists to have a basic understanding of how the budget is prepared. It is becoming more commonplace that health information supervisors have access to software for management functions. This project will build upon the knowledge you have gained regarding the use of automated spreadsheets. INSTRUCTIONAL GOALS 1. To provide practice using a spreadsheet in department budget preparation. 2. To describe the budget variance reporting process. STUDENT OBJECTIVES: 1. Given necessary data, calculate salary, service, supply, education and travel expenses. 2. Develop the monthly and annual HIM department budget using an automated spreadsheet. BUDGET 10 9.3 9.0 8.8 8.5 7.5 6.5 0 GRADING: = 0 errors = 1 error = 2 errors = 3 errors = 4 errors = 5 errors = 6 errors = 7 or more errors 2 INSTRUCTION SHEET 1. You are to calculate a monthly and annual Health Information Department budget from the information provided you about Community Hospital using an automated spreadsheet and give the printed worksheet to the instructor. a. Read information sheet #1. b. Before going to the computer, calculate the following and write in the margin of the appropriate section of the lab project the amount you will eventually enter into the computer: - c. The costs for vacation relief for each month Xeroxing Travel Materials and supplies Machine maintenance Telephone Prepare the budget using Excel software. The spreadsheet format should appear as on page 5. 2. You wish to add one employee to the department starting March 1, salary to begin at $2,233 per month with a $120 monthly step increase after six months and on his/her anniversary date thereafter. 3. Submit the budget in landscape mode, with small enough font so that it can appear all on one page. 4. Complete a graph of the 12-month budget trend for the major categories of the budget. Make sure to following good graphing principles and include a title for your graph as well as axis labels. Include graph on separate page /tab. 5. Now it is February and your first month of budget actuals for January is available. How would you evaluate your budget results? Review the information on budget variance reporting in your text. Provide a short response of about 250 words that addresses this topic. Submit your complete Excel file as well as a Word document that includes a copy of your budget, the graph, and response to the budget variance question. INFORMATION SHEET #1 OVERVIEW OF COMMUNITY HOSPITAL Community Hospital has 115 beds and 25 bassinets. By the end of this year it is expected that there will be 9,670 adults and children discharged and 1,460 newborn discharged from the hospital. Next year, 5 new beds will be added to the hospital and a 6% increase in adult and child patients is expected. 3 The Health Information Department budget for the next year will be computed utilizing the following information. The fiscal year is January 1, 201x through December 31, 201x. 1. Salaries: Each employee (including the new one) will receive a 3% cost of living raise on July 1. In addition, each employee will receive a step increase on his/her anniversary date. Hospital policy states that if the anniversary date falls between the first and the fifteenth of the month, the step increase will be effective the first day of the month. If the anniversary date falls between the sixteenth and the thirty-first of the month, the step increase will be effective the first day of the following month. Below are the names of the 5 employees, their anniversary dates, the salary they are now receiving per month, and the amount of their step increases per month. Present Salary Step Employee Anniv. Date Per Month Increase Thomas Weatherby Mai Cheng Anna Fernandez Jefferson Taylor Elizabeth Adams 8-4-05 2-16-07 9-20-06 6-9-09 11-15-07 $4,290 3,600 2,850 2,600 1,820 $200 180 140 120 80 It is the hospital policy that new employees are on probation for 6 months. If they have successfully completed their probationary period at that time they will receive a step increase, another on their anniversary date and then on their anniversary date thereafter until they reach their top step. You plan to budget vacation relief (2 weeks plans given to you by your employees: - March: 5 days July: - May: 10 days August: - June: 15 days December: each person) as follows based on early 10 days 5 days 5 days You plan to use a former employee next year as you did this year to cover time off for those on vacation. This former health information technician employee is paid $16.00 per hour and works an 8-hour day. This temporary employee will not receive a salary increase in July. Payroll taxes of 7.5% and employee benefits of 23% are each calculated based on the total gross salaries for the month and for the year. 2. Photocopying: 5 cents per copy. Average 10 insurance requests per week and 5 sheets copied per request. Average 15 requests per week for full copies of charts about 50 sheets per chart. Miscellaneous 2,000 copies per year. Figure an equal amount per month. 3. Travel: Conference in Chicago for one person (Thomas) for 3 days in February. He will travel on a Sunday. Meetings are Monday through Wednesday noon. Flies back Wednesday afternoon. Airfare: $650 round trip. $50 per day for food. $180 per night for hotel. $425 registration fee. $125 miscellaneous (shuttle to and from airports, taxi, tips, etc.). Educational meeting in Portland in August for 2 people (Mai and Elizabeth) for 2 days. Will drive down Wednesday. Meetings are Thursday and Friday. Will drive back Friday evening. 200 miles one way to Portland (will drive). 39 cents per mile. $50 per day for food per person. $130 per night for 2-bed double room at motel. 4 Both will stay in one room. $125 per person registration fee. $20 per person miscellaneous. All travelers will arrive at the hotel the night before the meeting and leave late afternoon or evening the last day of the meeting. Food allotment will be for travel days as well as meeting days. 4. Materials and supplies: $10,040 last year. Consider 10% increase this year for cost of supplies. Figure an equal amount per month. 5. Machine maintenance: All of the following figures are on a per year basis. Last year, the costs were - $450 per computer (5 computers in the office). 2 printers at $35 each. Photocopier at $350 total. Consider a 5% increase for next year. Figure an equal amount per month. 6. Long distance telephone calls: Average 20 per month at $5.75 each. Figure an equal amount per month. Continue on next page… 5 6 INFORMATION SHEET #2 EXCEL INSTRUCTIONS FOR BUDGET COMPLETION Go to Page Layout/Orientation/Landscape. Go to Scale to fit/Page/Fit to 1 page by 1 tall. Prepare the column and row headings as shown on the template on page 5. To prepare the columns going across the spread of pages: A1 To change column width to 27 spaces Home/Cells/Format/Column Width/27 B1 Change column width of remaining columns to 12 spaces Highlight B1-N1, Home/Cells/Format/Column Width/12 B9 Center justify all cells B9 through N9. Highlight B9 through N9, Home/Center justify icon B9 In capitals type JAN. Then “snag” the fill handle and move across to M9 and let go. The names of each of the months should automatically appear. Have it print gridlines. Make thick lines as shown on page 5. Have thick boarders surrounding the document Have all figures with commas at thousands with no decimal places. SAVE YOUR WORK Entering Figures and Formulas. You must absolutely use the cell formulas as stated. Bypassing the cell formulas and just entering numbers will get you into trouble later when you want to place the percentage cost of living increase. SAVE AFTER EACH ONE OR TWO EMPLOYEES YOU ENTER. Step A10 THOMAS WEATHERBY B10 4290 C10 =B10 C10 Snag fill handle and highlight C10 through H10 and let go of mouse I10 = H10+200 J10 = I10 J10 Snag fill handle and highlight J10-M10 and let go of mouse B10 B11 C11 D11 MAI CHENG 3600 =B11 =C11+180 E11 =D11 E11 Snag fill handle and highlight E11-M11 Explanation Starting salary To make later calculations work Salary appears automatically Add the raise in August Copy the number in I10 (not formula) New salary appears automatically Starting salary To make later calculations work Raise (place in March since anniversary date is 2/16) Copy the number in D11 (not formula) New salary appears automatically 7 and let go of mouse ANNA FERNANDEZ B12 2850 C12 =B12 C12 Snag fill handle and highlight C12-J12 and let go of mouse K12 =J12+140 L12 = K12 L12 Snag fill handle and highlight L12 -M12 and let go of mouse JEFFERSON TAYLOR B13 2600 C13 = B13 C13 Snag fill handle and highlight C13-F13 and let go of mouse G13 =F13+120 H13 =G13 H13 Snag fill handle and highlight H13 - M13 ELIZABETH ADAMS B14 1820 C14 = B14 C14 Snag fill handle and highlight C14-K14 and let go of mouse L14 =K14 + 80 M14 =L14 Starting salary To make later calculations work Salary appears automatically Raise (place in October since anniversary date is 9/20) Copy the number in K11 (not formula) New salary appears automatically Starting salary To make later calculations work Salary appears automatically Raise Copy the number in G13 (not formula) New salary appears automatically Starting salary To make later calculations work Salary appears automatically Raise Copy the number in L14 (not formula) which is the new salary NEW EMPLOYEE Remember, this employee doesn't start work until March D15 2233 Starting salary E15 =D15 To make later calculations work E15 Snag fill handle and highlight E15-I15 Salary appears automatically J15 =I15+120 Raise K15 =J15 Copy the number in J15 (not formula) K15 Snag fill handle and highlight K15-M15 New salary appears automatically and let go of mouse VACATION RELIEF Calculate the amount to budget for each of the six months as described on page 3 of your lab project. Enter those figures in the appropriate cell blocks in row 16. SAVE YOUR SHEET COST OF LIVING INCREASE H10 =G10*1.03 H10 Snag the fill handle, highlight H10-H15 and let go of mouse. Be careful; do not highlight H16 as that is the vacation relief who does not receive the cost of living increase. 8 TOTAL N10 =sum(B10:M10) N10 Snag fill handle and highlight N10-N16 and let go of mouse B18 =sum(B10:B16) B18 Snag fill handle and highlight B18-N18 and let go of mouse PAYROLL TAXES B19 =B18*0.075 B19 Snag fill handle and highlight B19-N19 and let go of mouse FRINGE BENEFITS B20 =B18*0.23 B20 Snag fill handle and highlight B20-N20 and let go of mouse TOTAL PERSONNEL B22 =sum(B18:B20) B22 Snag fill handle and highlight B22-N22 and let go of mouse PHOTOCOPYING B26 Calculate the average monthly cost, enter that amount. Snag fill handle and highlight B26-M26 and let go of mouse TRAVEL Calculate the costs for the Chicago workshop and enter that in C27 (February). Calculate the costs for the Portland workshop and enter that in I27 (August) MATERIALS AND SUPPLIES B28 Calculate the monthly amount, enter that amount. Snag fill handle and highlight B28-M28 and let go of mouse MACHINE MAINTENANCE B29 Calculate the monthly amount, enter that amount. Snag fill handle and highlight B29-M29 and let go of mouse. TELEPHONE B30 Calculate the monthly amount, enter that amount. Snag fill handle and highlight B30-M30. TOTAL OTHER DIRECT COSTS B32 =sum(B26:B30) B32 Snag fill handle and highlight B32-N32 and let go of mouse N26 =sum(B26:M26) N26 Snag fill handle and highlight N26-N30 and let go of mouse TOTAL OPERATING BUDGET B37 =sum(B22+B32) Please note, this is a + sign, not a colon (:) in the formula B37 Snag fill handle and highlight B37-N37 and let go of mouse REPAIR any border lines that might have disappeared or changed from thick to thin as a result of your work. SAVE YOUR WORK PRINT - It should now fit all on one page. Check print view first.