Homework answers / question archive / Using an Excel spreadsheet, a year one statistical report of projected volume, reimbursement, revenue, and expenses

Using an Excel spreadsheet, a year one statistical report of projected volume, reimbursement, revenue, and expenses

Finance

Share With

---

• • Using an Excel spreadsheet, a year one statistical report of projected volume, reimbursement, revenue, and expenses. Exhibit 10.1 in the textbook provides an excellent reference for your projected revenue analysis.
  • A detailed list assumptions to provide justification for all year one projected volumes, reimbursement, revenue, and expenses, as well as inflation and cost of capital
  • Using an Excel spreadsheet, a cash flow analysis of revenues and expenses for years zero, one, two and three. This will produce the net cash flow for your project and enable you to calculate the breakeven point and profitability measures. Exhibit 10.2 in the textbook provides an excellent reference for your cash flow analysis.
  • Using an Excel spreadsheet, a breakeven point calculation (years/months), a net present value (NPV) calculation, and an internal rate of return (IRR) calculation

. You have been retained as a financial consultant. Your task is to conduct a complete financial analysis of one of the capital projects and then present your findings and recommendations to the board of directors

Additional information and resources are presented below.

 

Your cash flow analysis will include expenses incurred prior to the start date of your project (year zero), including facility renovation, FTE expenses during startup, education, training, etc. If you are replacing a piece of equipment, include any salvage value. Maintenance contract expense (if appropriate) should not be included in year zero.

 

Realistic assumptions should be identified for your projected volume and annual growth, as well as your inflation factor for operating expenses. Once you have the volume projections, identify the procedural codes (i.e., CPT/DRG) which you will use for billing the services delivered.

 

I've attached a "Hospitalist" project which may be helpful. It explains the process used by the author to arrive at volume, reimbursement, FTE's, etc. ProjectingHospitalistProgramVolume.pdf

 

A key point for this assignment is that the project you choose will generate reimbursable revenue for the hospital. This is revenue generated by billing payers (government and commercial) for services rendered. You may build in a small percentage of self pay revenue into your payer mix. Simply assuming revenue will come from non-operating services, endowments, or fundraising will not suffice for this assignment.

 

A good resource for determining revenue is the "Medicare Fee Schedule", which details provider reimbursement according to Current Procedural Terminology (CPT) Codes.  You can find this information at the "Medicare" website. A reasonable assumption to make is that Medicaid reimburses at approximately 80% of the Medicare rate, while commercial insurers typically reimburse at 105% to 110% of the Medicare rate. Once you arrive at your Payer Mix and determine the Medicare reimbursement rates for the services you are providing, you can then calculate the reimbursement for Medicaid and Commercial Insurance.  You may add a revenue figure for "Self-Pay" patients, typically a discounted % of charges.

 

When calculating the fringe benefits cost for any projected FTEs in your operating budget, use 20% of salary.

 

When calculating NPV and IRR, you will need the cost of capital.  Here is a resource that might be helpful.

http://people.stern.nyu.edu/adamodar/New_Home_Page/datafile/wacc.htm