Use the Assignment on this website: https://bookshelf.vitalsource.com/home/dashboard?context=login
The instructions for this assignment are also found in Chapter 16 of your required textbook.
AssiUsegnment 2 Financial Analysis Project Phase II Completion requirements
Assignment 2 Overview
Now that you’ve set your workbook up and have completed the SurgiFlex calculations in Phase I, you can start completing the consolidated worksheets with the relevant calculations and bring in the master supply list! The second phase starts with the supply list because it’s also linked into the consolidated sheets (like the rest of the SurgiFlex Phase I calculations).
Assignment 2 Instructions
Phase II is the “consolidated” phase of the project because it incorporates the SurgiFlex data into the organization-level data. Note that SurgiFlex figures from Phase I do not change with seasonality percentages. As with the Phase I calculations, there should be categories, monthly calculations, and totals on all worksheets, with the exception of the master supply list.
Note: The instructions for this assignment are also found in Chapter 16 of your required textbook.
Worksheet #11: Master Supply List
The master supply list is the supply list for the entire healthcare organization and will be referenced/linked into Worksheet #10. For now, simply copy and paste the master supply list provided in Exhibit 16A.3 (see Chapter 16 Appendix, Exhibit 16A.3). Be sure to add a heading/title block and do a summation on total costs.
Worksheet #7: Consolidated Operating/Utilization Data
This worksheet combines the SurgiFlex operating data from Worksheet #1 with the rest of the healthcare organization’s operating data (patient/procedure volumes). To create this worksheet, use the data and format shown in Exhibit 16A.4 (see Chapter 16 Appendix, Exhibit 16A.4). The data provided is for the first month of the fiscal year (July). After the first month of data has been completed, the remaining months must increase or decrease to reflect seasonality. The increases or decreases should be entered as formulas and should be based on the prior month (August changes based on July, September changes based on August, etc.). The seasonality changes are:
- Aug to Oct: ↓2%
- Nov to Jan: ↑7%
- Feb to Apr: ↓4%
- May to June: ↑11%
Worksheet #8: Consolidated Salary and Wage Calculations
This data represents the medical (non-SurgiFlex) salaries from the rest of the organization, given for month one (July). Remaining months of medical salaries must fluctuate with the same percentage changes used in Worksheet #7. Increased cases/volume often means increased labor hours in the clinical setting, so labor dollars must change as well. SurgiFlex salaries must be linked in from Worksheet #2.
- medical salaries: $2,495,500
- surgical salaries: $3,750,000
- fringe benefits: 33% of salaries
Worksheet #9: Consolidated Revenue Calculations
Revenue (non-SurgiFlex) is given for month one in Table 16.3. Remaining months must fluctuate with the same percentage changes used in Worksheet #7. SurgiFlex revenue must be referenced/linked in from Worksheet #3.
Worksheet #10: Consolidated Nonsalary Expense Calculations
Expenses noted in the list that follows are given for month one. Remaining months must fluctuate with the percentage changes used in Worksheet #7. SurgiFlex supplies must be linked in from Worksheet #4. In addition, the following items must be represented:
- facility maintenance: $735,000
- utilities: $80,000
- patient transportation: $165,000
- marketing: $100,000
- supplies total: linked total from the master supply list (Worksheet #11)
- vendor total inventory costs (chosen vendor linked from Worksheet #6)
Submission Criteria for Assignment 2
- The Excel workbook and worksheets should be labeled according to the above instructions.
- Only your course textbook is required for this assignment as a reference.
- Adhere to grammar, spelling, and punctuation criteria as applicable.
- Adhere to APA compliance guidelines as applicable.
