HSMP 587 - Health Care Financial Management 2/6/17
Winter 2017 Dr. Neal Wallace
Take Home Mid-Term Exam
Generic Directions: The exam is expected to be completed independently by each student. The exams are due by class on February 13. E-mail it to me using the name MDT-lastname-587.xls. The answer to the first question can be done separately as a WORD document if you’d like – use the same naming convention. To show your work process (not answers) for question #5 where you make adjustments to our original model you can name and submit that spreadsheet as MDTQ5-lastname-587.xls. There are 5 questions at 20 points each. They are based on the materials we have developed so far in class. Please use our last budget spreadsheet (for Feb. 6th class) – HFMR&EthruCapplanningW17.xls. Rename this file as noted above and put all your exam answers on a separate worksheet tab that I could print out if necessary. (You can refer me to other places that you do work e.g. “new” cash budgets, but put the “final” summary answers in one sheet.) Questions may have multiple parts. Read each question carefully and be sure that you respond completely to each. For answers to the questions that require calculation, make sure I can identify the formula or methods used to obtain your answer. Make sure your answers are printable.
1) a) What are the five types of budgets we have discussed?
b) What accounting method does each use?
c) What does each do?
d) In what ways are they interrelated i.e. how does one affect the other, if at all (be thorough)?
e) What does it mean to budget “conservatively” and why is that important?
2) Assume that you will replace all your equipment at the beginning of your seventh year (2023) of operation (after all of it is fully depreciated – remember it was partially depreciated in the first year).
a) What is the value of your depreciation expense for your original equipment in the sixth year (2022)?
b) Suppose you have fully funded your depreciation expenses each year (i.e. collected cash equal to your budgeted depreciation levels) and at year end you can invest this cash at 3% annual (compounded) return. How much cash would you have from this (depreciation fund + interest accrued) by the end of the sixth year (2022)?
c) Given the value of your depreciation fund plus interest that you accrue through 2022, what level of average annual price inflation could this amount support in purchasing new equipment at the beginning of 2023?
3) Assuming that our business continues as planned so far (i.e. 2018 is full capacity):
a) What are the only two expense items that will change in value from 2018 through 2022 assuming no equipment replacement until 2023 (i.e. from the second to the sixth year of operation)?
b) What are our expected total expenses and revenues for each of the years 2018-2022 (no equipment replacement until 2023)?
c) What are our revenue per visit, cost per visit and difference (rev/visit-cost/visit) in 2018-2022?
d) How are we looking as a business by this measure over this period, generally?
4) Copy the 2019 cash budget twice on the same worksheet just below were it is.
a) Re-label and modify these to represent an appropriate 2020 and 2021 cash budget, assuming no new equipment purchases or other changes in our business (I can just look at the cash budget tab here, do not move this to your final answer section).
b) Add columns to your capital budget for 2020 and 2021 and modify formulas (cells pointed to) as appropriate to reflect capital activity in these years (as in a) above, you do not need to move this part).
b) How much “free” cash do we have available to purchase new equipment at the end of 2021 (assume all “free” cash can be used to buy equipment)?
c) Suppose that you knew that average annual price inflation on your equipment from 2017-2021 would range between 3% and 5%. How much of your year-end free cash in 2021 should you allocate to your equipment reserves (depreciation plus other) to be prepared to buy new equipment in 2022, and why?
5) Assume you’re back in the planning stages and you’ve heard about a new lab testing machine that is more productive than the ones you originally planned for. They cost $100,000 to buy, have a 5 year useful life and will allow a lab tech to produce one additional lab test per hour (four instead of three). Using another (renamed) copy of HFMR&EthruCapplanningW17.xls, make the following adjustments and record your answers.
a) Assume you will purchase this new equipment instead of the original. Change your expense statistics accordingly and identify which expense items other than debt payments change in 2017 and/or 2018, in which direction (up or down) and why.
b) Now adjust the positive monthly capital inflow amounts in your 2017 cash budget (months 1-9 in 2016 only) by $5,000 increments (up or down) so that you have at least $149,000 but not more than $154,000 net cash each month. What is our total (maximum) debt (e.g. principal borrowed) under this new scenario? How does it compare to what we expected to borrow originally? What are our debt payments for 2017-2019 under this new scenario and how do they differ from the original (do not change our monthly principal payments)? What are our year-end capital reserves for 2017-2019 and how do they differ from the original?
c) Now look at your new capital planning model (“for midterm”) and report your “new” and “original” NPV and profitability ratios. Based on these figures, should you invest in the new equipment or not? What made this more/less profitable than the original model?