Poste Excel File Edit View Insert Format Tools Data Window Help AutoSave OFF Calibri (Body) [A v BI U v 16 ' ' v Analytical Exercise HSA 304 Spring 2022 (4)- done Wrap Text General W Y v Merge & Center $%9 00 Cell Conditional Format Formatting as Table Styles Insert Delete Format v Sort & Filter Tue May 3 12:44 PM Find & Select Analyze Data 17 fx A C D E F G H J K L M N 0 P a R S T U V Vi x Y 3 4 5 2 Budget vs Actual Worksheet You are responsible for completing a budget for an Ambulatory Clinic. Here are the assumptions: 6 7 INITIAL BUDGET 8 9 a. The budget anticipated 65,000 inpatient days this year at an average of $960 per day. 10 b. Inpatient expenses were budgeted at $850 per day. 11 12 c. The budget anticipated 27,000 outpatient visits this year at an average of $675 revenue per visit. 13 d. Outpatient expenses were budgeted at $520 per visit. 14 15 ACTUAL RESULTS 16 17 18 19 b. The average revenue of $960 per day will be achieved for these 56,550 inpatient days. a. Assume that only 56,550 or 87% of the inpatient days are going to actually be achieved for the year. 56550 20 c. The Outpatient visits will actually amount to 120% of budget or 30,240 for the year. 30240 21 d. The average revenue of $475 per visit will be achieved for these 30,240 visits. 22 23 24 e. Actual inpatient expenses will amount to $14,600,000 and actual outpatient expenses will amount to $5,220,000. 25 REQUIRED: A. Prepare the budget and the actual on the attached worksheet. 26 27 28 29 30 31 32 33 34 Depreciation FTE BvAStatic BvAStaticWorksheet Payback URR + Ready 100% 2 3 4 AutoSave OFF Home Insert Draw Page Layout X Calibri (Body) v Poste B I U v J6 5 6 7 9 10 11 12 13 14 15 16 17 11 19 20 21 22 23 24 25 3G 27 28 39 30 31 32 33 34 15 36 17 38 19 40 41 42 43 44 45 40 47 * x fx = Analytical Exercise HSA 304 Spring 2022 (4)- done Formulas Datal Review View Tell me Share Comments v 16 A A Wrap Text Custom W & A Merge & Center $%9 00 Conditional Format Formatting as Table Styles Cell Insert Delete Format v Sort & Filter Find & Select Analyze Data D [ F G AS BUDGETED Revenue Inpatient Revenue Outpatient Subtotal Expenses Inpatient Expenses - Outpatient Subtotal Excess of Revenue over Expenses ACTUAL Revenue Inpatient Revenue Outpatient Subtotal Expenses Inpatient Expenses Outpatient Subtotal Excess of Revenue over Expenses L Depreciation FTE BvAStatic BvAStaticWorksheet Payback URR + Ready 14 2 R R 5 T V W X Y An AD AC AD # a 78%