finance excel

HCMN415 (SP19) Excel Workbook Requirements

The daVinci Surgical System was purchased by your hospital. It was an expensive capital purchase and hospital executives aren’t totally convinced it was worth it. They’ve asked you to compile and present data for the first year of operation, to help them determine whether they’ll keep the daVinci Surgical System moving forward or liquidate it.

The financial analysis workbook that you’ll create over the course of the semester includes standard operating and financial data, as well as daVinci specific information. The data listed below is to be used for the various calculations throughout the financial analysis workbook, and referenced in the business plan portion of the project. There will be several additional calculations that you must perform, to complete the workbook.

Name Your Workbook – begin with your last name, followed by the name of your hospital/healthcare organization. (Henson – Henson Research Hospital).

Financial Analysis Workbook – MUST contain the following worksheets/tabs (names can be abbreviated as needed):

1. Statement of Operations (Income Statement)

2. Balance Sheet

3. Statement of Cash Flows

4. Financial Forecast

5. Consolidated Operating Data

6. Consolidated Salary and Wage Calculations

7. Consolidated Revenue Calculations

8. Consolidated Expense Calculation

9. daVinci Operating Data

10. daVinci Salary and Wage Calculations

11. daVinci Revenue Calculations

12. daVinci Expense Calculations

13. daVinci Break-Even Analysis

14. Supply List (for VLOOKUP and Pivot Table)

15. Ratios Calculations

16. Performance Measurement Dashboard (Graphs and Visual Elements)

17. Notes (Not graded; Use as needed)

The financial statements and other worksheets must have the correct heading, containing the following three elements:

- Organization Name (Henson Research Hospital)

- Title of Financial Statement (Statement of Operations)

- Time Period (ending) Represented (Fiscal Year Ending June 2018)

Statement of Operations (also called Income Statement): this is a summary statement for the entire 12-month period (as opposed to monthly on the calculations worksheets). The data comes from the revenue and expenses worksheets, as well as data provided below. For purposes of this project, the data must be summarized quarterly using excel formulas (required).

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.

· 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

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.

· New equipment purchase (daVinci): $2,000,000

· Salvage receipts (old equipment): $1,500,000

· Cash from patients (copays): $225,000

· Interest paid: $10,000

· Taxes Paid: $0.00

· Fund raising/charity donations and receipts: $500,000

· Bonds/securities purchase: $500,000

· Interest earned: $750,000

ProForma Forecast – 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: ↑5% annually

· Outpatient revenue: ↑1% annually

· daVinci cases: ↑3% annually

· All salaries: ↑2% annually

· All supplies: ↑1% annually

All other line items remain static.

Consolidated Operating Data – as previously done; includes the daVinci operating data (inpatient days) from the appropriate worksheet. (Must be linked in using an excel formula). The escalations to be used are:

· July – Oct: ↓5%

· Nov – Jan: ↑7%

· Feb – Apr: ↓3%

· May – June: ↑13%

Consolidated Salary and Wage – medical (non DaVinci) salary given for month one. Remaining months of medical salaries must fluctuate with the percentage change of consolidated operating data. DaVinci salaries must be linked in from the operating data worksheet.

· Med surg July salary: $2,750,000.

· Fringe Benefits: 33% of salaries

· Must contain medical, daVinci, and fringe.

Consolidated Revenue Calculations – Revenue is given for month one. Remaining months must fluctuate with the percentage change of consolidated operating data. DaVinci revenue must be linked in.

· Inpatient Revenue: $4,250,000

· Outpatient Revenue: $2,750,000

· Ancillary Revenue: $522,000

Consolidated Expense Calculations – med-surg expenses given for month one. Remaining months must fluctuate with the percentage change of consolidated operating data. DaVinci supplies must be linked in from daVinci data worksheet.

· Med surg July (non-salary) expenses:

Maintenance: $250,000

Utilities: $50,000

Transportation: $75,000

Supplies: $58,000

daVinci Operating Data – calculate the number of cases as done previously in class. That number should link into the consolidated operating data worksheet. This data should be shown monthly.

daVinci Salary Expense – For each daVinci surgical case, the following personnel/hours must be applied:

· (1) MD/Surgeon @$325/hour

· (1) RN @$80/hour

· (2) Surgical Techs @$45/hour

daVinci Revenue – revenue is $8,500 per case.

daVinci Expense (non sal) – this worksheet should contain supply costs per case as well as the labor calculations per case. This data must be shown monthly.

· Supply costs per daVinci case: $3,500

· Annual maintenance costs: $185,000

daVinci Break-Even Analysis – this must include all costs associated with the implementation of the surgical system. Initial costs, all expenses, all revenues, etc. How many cases must be performed to break-even?

Supply List – Separate Excel file

Financial Ratios – TBD

Dashboards/Performance Measures – the dashboard/performance measurement graphs will be based on the data calculated on the various worksheets of your workbook. We’ll explore the various types of graphs in excel, then pull in the data from the worksheets. Required dashboard items (separate graphs) are:

· Consolidated revenue (showing each service line)

· Consolidated expense (showing each service line)

· daVinci data (revenue, expense and profit margin)

***Excel Workbooks without formulas or those missing entire worksheets will NOT receive a passing grade.

Dr. Henson