HCMN415 (SP20 Phase III Workbook Requirements Now that you've completed the Da Vinci and Consolidated calculations, you can begin the final phase of the workbook - the financial statements and performance measurement items. Remember, you've already done most of the work for these, now it's time to pull it all together. 1. Statement of Operations (also called Income Statement): this is a summary statement for the entire 12-month period. The data comes from the consolidated revenue and consolidated expenses worksheets. For purposes of this project, the data must be summarized quarterly using Excel formulas (required). 2. Balance Sheet: this financial statement details the hospitals assets and liabilities. Use the following data for this financial statement...see formatting examples in the textbook. Just use these categories, no other data is required at this time. (Summarized annually using formulas). Cash and Cash Equivalents: 7,500,000 Prepaid expenses: $25,000 Land: $2,000,000 Amortization: $10,000 Accounts Payable: $50,000 Notes Payable: $10,000 Equipment (Non DV): $5,000,000 3. Statement of Cash Flows-this financial statement details the cash transactions of the hospitals (inflows and outflows). Use the following data for this financial statement... operations, investing and financing subsections must be used. See formatting examples in the textbook. Just use these categories, no other data is required at this time. (Summarized annually using formulas). - - - - - - New equipment purchase (daVinci): $2,500,000 Salvage receipts (old equipment): $1,500,000 Cash from patients (copays): $250,000 Interest paid: $15,000 Taxes Paid: $0.00 Fund raising/charity donations and receipts: $500,000 Bonds/securities purchase: $500,000 Interest earned: $750,000 4. ProForma Forecast - this financial statement is a forecast, anticipating future financial performance. Link in the annual totals for each category from the Statement of Operations. For the financial projections, incorporate the following anticipated changes for the next four years: Inpatient revenue: 15% annually Outpatient revenue: f1% annually daVinci cases: f3% annually All salaries: 12% annually All supplies: 11% annually All other line items remain static. 5. Financial Ratios financial ratios are measures of an organizations performance, and use figures that are already represented in the financial statements. Financial ratios should be calculated after you've created the financial statements. The formulas for these ratios are in Chapter 13 of the textbook. The following ratios must be represented in your workbook: Total Margin Return on Assets Debt Ratio DuPont Equation 6. Dashboards/Performance Measures - the dashboard/performance measurement graphs will be based on the data calculated on the various worksheets of your workbook. Your graphs can be any style: line, bar, combined, etc. Required dashboard items (separate graphs) are: Consolidated revenue (showing each service line) Consolidated expense (showing each service line) DaVinci revenue DaVinci expense DaVinci profit margin HCMN415 (SP20 Phase III Workbook Requirements Now that you've completed the Da Vinci and Consolidated calculations, you can begin the final phase of the workbook - the financial statements and performance measurement items. Remember, you've already done most of the work for these, now it's time to pull it all together. 1. Statement of Operations (also called Income Statement): this is a summary statement for the entire 12-month period. The data comes from the consolidated revenue and consolidated expenses worksheets. For purposes of this project, the data must be summarized quarterly using Excel formulas (required). 2. Balance Sheet: this financial statement details the hospitals assets and liabilities. Use the following data for this financial statement...see formatting examples in the textbook. Just use these categories, no other data is required at this time. (Summarized annually using formulas). Cash and Cash Equivalents: 7,500,000 Prepaid expenses: $25,000 Land: $2,000,000 Amortization: $10,000 Accounts Payable: $50,000 Notes Payable: $10,000 Equipment (Non DV): $5,000,000 3. Statement of Cash Flows-this financial statement details the cash transactions of the hospitals (inflows and outflows). Use the following data for this financial statement... operations, investing and financing subsections must be used. See formatting examples in the textbook. Just use these categories, no other data is required at this time. (Summarized annually using formulas). - - - - - - New equipment purchase (daVinci): $2,500,000 Salvage receipts (old equipment): $1,500,000 Cash from patients (copays): $250,000 Interest paid: $15,000 Taxes Paid: $0.00 Fund raising/charity donations and receipts: $500,000 Bonds/securities purchase: $500,000 Interest earned: $750,000 4. ProForma Forecast - this financial statement is a forecast, anticipating future financial performance. Link in the annual totals for each category from the Statement of Operations. For the financial projections, incorporate the following anticipated changes for the next four years: Inpatient revenue: 15% annually Outpatient revenue: f1% annually daVinci cases: f3% annually All salaries: 12% annually All supplies: 11% annually All other line items remain static. 5. Financial Ratios financial ratios are measures of an organizations performance, and use figures that are already represented in the financial statements. Financial ratios should be calculated after you've created the financial statements. The formulas for these ratios are in Chapter 13 of the textbook. The following ratios must be represented in your workbook: Total Margin Return on Assets Debt Ratio DuPont Equation 6. Dashboards/Performance Measures - the dashboard/performance measurement graphs will be based on the data calculated on the various worksheets of your workbook. Your graphs can be any style: line, bar, combined, etc. Required dashboard items (separate graphs) are: Consolidated revenue (showing each service line) Consolidated expense (showing each service line) DaVinci revenue DaVinci expense DaVinci profit margin