1. Based on the monthly cash budget, what is the maximum loan balance for January? Based on the daily cash budget, what is the maximum loan balance for January? Why are the numbers different?2. Which budget should the clinic rely upon, the daily or monthly? Why?

3. Construct scenarios when billings are 5% higher and lower than the base case scenario here.

4. How do these scenarios change the maximum potential loan balances?5. What credit line would you recommend for the clinic, using the best and worst scenarios as well?

Mountain Village Clinic is a small walk-in clinic adjacent to the pri- mary ski area of Mountain Village, a winter resort located a short distance from Aspen, Colorado. It should be no surprise that the clinic specializes in the treatment of injuries sustained while skiing. It is owned and operated by two physicians: James Peterson, an orthopedist, and Amanda Cook, an internist. Patient volume at the clinic is highly seasonal because most of the business occurs during the ski season, which generally runs from December through March. In fact, at one time Drs. Peterson and Cook thought about closing the clinic during the slow months, but (1) the clinic would be dif- ficult to operate efficiently for only a portion of the year and (2) the area has started to attract a sizable amount of summer visitors, which has made summer operations more financially attractive.

The clinic has an outside accountant who takes care of payroll matters, but Dr. Cook does all the other financial work for the clinic. However, to help in that task, the clinic recently hired a part-time MHA student, Doug Washington. On a Wednesday afternoon in October 2017, Dr. Cook called Doug into her office to tell him about an upcoming meeting with the head of commercial lending at First Bank of Aspen, the clinic's primary lender. The purpose of the meeting is to discuss cash management services and the clinic's line-of-credit requirements. A line of credit is a short-term loan agreement by which a bank agrees to lend a business some specified maximum amount. The business can borrow (draw down) against the credit line at any time it is in force, which typically is no longer than one year. When a line expires, it must be renegotiated if it is still needed. The amount borrowed on the line, or some lesser amount, can be repaid at any time, but

any amount outstanding must be repaid at expiration. Interest is charged on the amount drawn down, and a commitment fee often is required up front to secure the line. In general, lines of credit are used by businesses to meet temporary (usually seasonal) cash needs as opposed to permanent long-term financing purposes. Currently, the annual interest rate on a First Bank line of credit is 4 percent (compounded monthly), and First Bank pays a 2 percent annual interest rate (compounded monthly) on temporary deposits of excess cash in a savings account. Interest earned (paid) occurs in the month following the end-of-month cash surplus (loan requirement).

To prepare for the upcoming meeting with the bank, Dr. Cook asked Doug to develop a cash budget and determine the line-of-credit amount the clinic should request. No one had taken the time to make cash budget recently, although a spreadsheet model that had been constructed a few years ago was available for use. From information previously developed, Doug knew that no seasonal financing would be needed from First Bank before January, so he decided to restrict his budget to the period of January through June 2018.

As a first step, Doug looked through the clinic's financial records to get the data needed to develop the billings forecast, which is contained in exhibit 26.1. To start, he assumed that actual billings as a percentage of fore- cast would be 100 percent. On the basis of the clinic's previous collections experience, he was able to convert billings for medical services into actual cash collections. On average, about 25 percent of the billings are collected in the month of billing (1-30 days), 30 percent in the first month after bill- ing (31-60 days), and 40 percent in the second month after billing (61-90 days); 5 percent of the billings are never collected (bad debt).

Variable medical costs at the clinic are assumed to consist entirely of medical and administrative supplies. These supplies, which are estimated to cost 15 percent of billings, are purchased two months before expected usage. On average, the clinic pays about half of its suppliers in the month of purchase (two months before use) and the other half in the following month (one month before use).

Clinical labor costs (for physicians and other clinical employees) are the primary expense of the clinic. During the high season (December through March), these costs run $300,000 a month, but some of the clinical staff work only seasonally, so clinical labor costs drop to $240,000 a month in the remaining months.

The clinic pays fixed general and administrative expenses, including clerical labor, of approximately $60,000 per month, and a lease payment

amounts to $24,000 per month. These expenditures are expected to con- tinue at the same level throughout the forecast period. The clinic's monthly miscellaneous expenses are estimated to be $20,000.

The clinic has a semiannual, five-year, 5 percent, $500,000 term loan outstanding with First Bank for which payments are due on March 15 and September 15. Also, the clinic is planning to replace an old X-ray machine (which has no salvage value) in February with a new one that costs $250,000. The clinic is a partnership, so, for tax purposes, any profits (or losses) are prorated to the two physician partners, who must pay individual taxes on this income. Thus, no tax payments are built into the clinic's cash budget.

The clinic has to maintain a target cash balance of $50,000 at First Bank because of compensating balance requirements on its term loan. This amount of cash, but no more, is expected to be on hand on January 1, 2018.

For the daily cash budget, Doug made the following additional assump- tions about volume and collections:

• Mountain Village Clinic operates seven days a week.
• Patient volume is more or less constant throughout the month,

so the daily billings forecast will be 1/(Number of days in the

month) multiplied by the billings forecast for that month.

• Daily billings follow the 25 percent-30 percent-40 percent

collection breakdown based on monthly billings.

• Collections are assumed to occur on the first day of the 1-30 days

collections category (the day of billing), the first day of the 31-60 days collections category (31 days after the day of billing), and the first day of the 61-90 days collections category (61 days after the day of billing).

• Lease payment is made on the 1st of the month.
• Fifty percent of both clinical labor costs and general and

administrative expenses are paid on the 1st of the month, and 50

percent are paid on the 15th of the month.

• Supplies are delivered on the 1st of the month and paid for on

the 5th of the month.

• Miscellaneous expenses are incurred and paid evenly throughout

each month.

• Term loan payments are made on the 15th of the month in which

they are due.

• Target cash balance of $50,000 must be in the bank on each day.

EXHIBIT 26.1 Mountain Village Clinic: Billings Forecast

Year

2017 2018

Month Amount

November $300,000

Dr. Cook is highly risk averse and worries about the accuracy of Doug's cash budget, particularly if the actual billings and timing of billings collections and bad debt are different from the forecast. In addition, she was recently informed by First Bank that it is reviewing the amount of compensating balance requirements on Mountain Village's term loan, which would, in turn, affect the target cash balance it maintains. Finally, the cash at the beginning is a rough estimate and could also be higher or lower than forecast.

Place yourself in Doug's position. Be prepared to discuss your analysis with Dr. Cook when you meet with her next week.

The model consists of a complete base case analysis--no changes need to be made to the existing MODEL-GENERATED DATA section. However, values in the INPUT DATA section of the student spreadsheet have been replaced by zeros. Students must select appropriate input values and enter Questions: them into the cells with values colored red. After this is done, any error cells will be corrected and 1. Based on the monthly cash budget, what is the maximum loan balance for January? Based on the daily cash the base case solution will appear. The KEY OUTPUT section includes the most important output from the MODEL-GENERATED DATA section. budget, what is the maximum loan balance for January? Why are the numbers different? INPUT DATA: KEY OUTRUT: 2. Which budget should the clinic rely upon, the daily or monthly? Why? Projected Billings: Projected Cost Data: Net Cash Gain (Loss): 3. Construct scenarios when billings are 5% higher and lower than the base case scenario here. 2017 Supply costs: Jani ($71,500) November $300,000 % of billings 15% Feb ($91,360) December $500,000 % paid 2 months before use 50% Mar $193,372 4. How do these scenarios change the maximum potential loan balances? % paid 1 month before use 50% Apri $225,220 2018 May i ($27,385) January $700,000 Fixed costs (monthly): Jun ($127,933) 5. What credit line would you recommend for the clinic, using the best and worst scenarios as well? February $900,000 Clinical labor - high season $300,000 March $600,000 Clinical labor - low season $240,000 Cumulative Surplus Cash April $300,000 General/admin expense $60,000 i (Loan Balance): May $200,000 Lease payment $24,000 June $400,000 Miscellaneous expense $20,000 Jan! ($121,500) July $500,000 Feb ($212,860) August $400,000 One-time costs: Mar ($19,488) New equipment $250,000 Apr $205,732 May $178,347 Actual Billings as a % of Forecast: 100.0% Jun $50,414 Billings Collections Data: % collected 1-30 days 25% % collected 31-60 days 30% % collected 61-90 days 40% % never collected (bad debt) 5%Cash Balance Data: Target cash balance $50,000 Cash at beginning SO First Bank: ine of credit periodic interest rate (monthly percentage rate) 4.0000% Savings account periodic interest rate (monthly percentage rate) 2.0000% Term loan payment (semi-annual) $56,614 MODEL-GENERATED DATA: Monthly Cash Budget: COLLECTIONS WORKSHEET November December January February March April May June July August Billed charges (expected) $300,000 $500,000 $700,000 $900,000 $600,000 $300,000 $200,000 $400,000 $500,000 $400,000 Billed charges (actual) $300,000 $500,000 $700,000 $900,000 $600,000 $300,000 $200,000 $400,000 $500,000 $400,000 Collections: 1-30 days $75,000 $125,000 $175,000 $225,000 $150,000 $75,000 $50,000 $100,000 31-60 days 90,000 150,000 210,000 270,000 180,000 90,000 60,000 61-90 days 120,000 200,000 280,000 360,000 240,000 120,000 Total collections $445,000 $635,000 $700,000 $615,000 $380,000 5280,000 SUPPLIES WORKSHEET Supplies purchases $105,000 $135,000 $90,000 $45,000 $30,000 $60,000 $75,000 $60,000 Supplies payments: 2 months before use $52,500 $67,500 $45,000 $22,500 $15,000 $30,000 $37,500 530,000 1 month before use 52,500 67,500 45,000 22,500 15,000 30,000 37,500 Total supplies costs $112,500 $67,500 $37,500 $45,000 $67,500 $67,500 NET CASH GAIN (LOSS) January February March April May June Total collections $445,000 $635,000 $700,000 $615,000 $380,000 $280,000 Payments: Supplies $112,500 $67,500 $37,500 $45,000 $67,500 $67,500 Clinical labor 300,000 300,000 300,000 240,000 240,000 240,000 General/admin expense 60,000 60,000 60,000 60,000 60,000 60,000 Lease payment 24,000 24,000 24,000 24,000 24,000 24,000 Miscellaneous expense 20,000 20,000 20,000 20,000 20,000 20,000 Term loan payment 56,614New equipment Total payments $516,500 $721,500 $498, 114 $389,000 $411,500 $411,500 Short-term interest paid or received (4,860) ! (8,514) 780) 4,115 3,567 Net cash gain (loss) ($71,500) ($91,360) $193,372 $225,220 ($27,385) ($127,933) BORROWING / SURPLUS SUMMARY January February March April May June Cash at beginning with no borrowing ($71,500) ($162,860 $30,512 $255,732 $228,347 Cash at end with no borrowing ($71,500) ($162,860) 1 $30,512 $255,732 $228,347 $100,414 Target cash balance 50,000 50,000 50,000 50,000 50,000 50,000 Cumulative surplus cash (loan balance) ($121,500) ($212,860 $19,488 $205,732 $178,347 $50,414 Daily Cash Budget: COLLECTIONS WORKSHEET Day 1 2 3 4 5 6 7 8 9 10 11 Billed charges: $22,581 $22,581 $22,581 $22,581 $22,581 $22,581 $22,581 $22,581 $22,581 $22,58 $22,581 Collections: 1-30 days $5,645 $5,645 $5,645 $5,645 $5,645 $5,645 $5,645 $5,645 $5,645 $5,645 $5,645 31-60 days 4,839 4,839 4,839 4,839 4,839 4,839 4,839 4,839 4,839 4,839 4,839 61-90 days 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 Total collections $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 SUPPLIES WORKSHEET Supplies payments: 2 months before use 45,000 1 month before use 67,500 Total supplies costs SO SO SO I $112,500 SO SO SO SO NET CASH GAIN (LOSS) Total collections $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 Payments: Supplies $112,500 Clinical labor $150,000 General/admin expense 30,000 Lease payment 24,000 Miscellaneous expense 645 $645 $645 $645 645 $645 $645 $645 $645 $645 $645 Term loan payment Total payments $204,645 $645 $645 $645 $113,145 $645 $645 $645 $645 $645 $645 Net cash gain (loss) ($190,161) $13,839 $13,839 $13,839 $98,661) $13,839 $13,839 $13,839 $13,839 $13,839 $13,839BORROWING / SURPLUS SUMMARY Cash at beginning with no borrowing SO ($190,161) ($176,323) ($162,484) ($148,645) ($247,306) ($233,468) ($219,629) ($205,790) ($191,952) ($178,113) Cash at end with no borrowing ($190,161) ($176,323) ($162,484) ($148,645) ($247,306) ($233,468) ($219,629) ($205,790) ($191,952) ($178,113) ($164,274) Target cash balance 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000 Cumulative surplus cash (loan balance) ($240,161) ($226,323) ($212,484) ($198,645) $297,306) ($283,468) ($269,629) ($255,790) (5241,952 $228,113) ($214,274) Day 1 2 3 5 6 8 9 10 11 Daily Cash Budget - Continued: Day 12 13 14 15 16 17 18 19 20 21 COLLECTIONS WORKSHEET Billed charges: $22,581 $22,581 $22,581 $22,581 $22,581 $22,581 $22,581 $22,581 $22,581 $22,581 Collections: 1-30 days $5,645 $5,645 $5,645 $5,645 $5,645 $5,645 $5,645 $5,645 $5,645 $5,645 31-60 days 4,839 4,839 4,839 4,839 4,839 4,839 4,839 4,839 4,839 4,839 61-90 days 4.000 4,000 4,000 4,000 4,000 4,000 4.000 4,000 4.000 4,000 Total collections $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 SUPPLIES WORKSHEET Supplies payments: 2 months before use 1 month before use Total supplies costs SO SO SO SO SO SO SO SO SO SO NET CASH GAIN (LOSS) Total collections $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 Payments: Supplies Clinical labor $150,000 General/admin expense 30,000 Lease payment Miscellaneous expense $645 $645 $645 645 $645 $645 $645 $645 $645 $645 Term loan payment Total payments $645 $645 $645 $180,645 $645 $645 $645 $645 $645 $645 Net cash gain (loss) $13,839 $13,839 $13,839 ($166,1 $13,839 $13,839 $13,839 $13,839 $13,839 $13,839 BORROWING / SURPLUS SUMMARYBORROWING / SURPLUS SUMMARY Cash at beginning with no borrowing ($164,274) ($150,435) ($136,597) ($122,758) ($288,919) ($275,081) Cash at end with no borrowing ($261,242) ($247,403) ($150,435) ($233,565) ($219,726) ($136,597) ($122,758) (5288,919) ($275,081) ($261,242) Target cash balance ($247,403) ($233,565) 50,000 50,000 50,000 (5219,726) (5205,887) 50,000 50,000 50,000 50,000 50,000 50,000 Cumulative surplus cash (loan balance) ($200,435) 50,000 ($186,597) ($172,758) ($338,919) ($325,081) ($311,242) ($297,403) ($283,565) ($269,726) Day 12 ($255,887) 13 14 15 16 17 18 19 20 21 Daily Cash Budget - Continued: Day 22 F 23 24 25 26 F 27 F 28 29 COLLECTIONS WORKSHEET 30 31 Billed charges: $22,581 $22,581 $22,581 $22,581 $22,581 $22,581 $22,581 $22,581 Collections: $22,581 $22,581 1-30 days $5,645 $5,645 $5,645 $5,645 $5,645 31-60 days $5,645 $5,645 $5,645 $5,645 4,839 4,839 $5,645 4,839 4,839 4,839 4,839 4,839 4,839 4,839 61-90 days 4,839 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 Total collections $14,484 $14,484 0 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $10,484 SUPPLIES WORKSHEET Supplies payments: 2 months before use 1 month before use Total supplies costs SO SO SO SO SO SO SO NET CASH GAIN (LOSS) Total collections $14,484 $14,484 31 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 $14,484 Payments: $10,484 Supplies Clinical labor General/admin expense Lease payment Miscellaneous expense $645 $645 $645 $645 $645 $645 $645 $645 Term loan payment $645 $645 Total payments $645 $645 $645 $645 $645 $645 Net cash gain (loss) $645 $645 $13,839 $13,839 $645 $13,839 $645 $13,839 $13,839 $13,839 $13,839 $13,839 $13,839 59,839BORROWING / SURPLUS SUMMARY Cash at beginning with no borrowing ($205,887) ($192,048) ($178,210) ($164,371) ($150,532) (5136,694) Cash at end with no borrowing ($192,048) ($178,210) ($122,855) ($109,016) ($164,371) ($95,177) ($150,532) (581,339) ($136,694) Target cash balance ($122,855) ($109,016) ($95,177) 50,000 ($81,339) 50,000 50,000 ($71,500) 50,000 50,000 50,000 50,000 50,000 Cumulative surplus cash (loan balance) ($242,048) 50,000 ($228,210) 50,000 ($214,371) ($200,532) ($186,694) ($172,855) ($159,016) ($145,177) ($131,339) Day 22 ($121,500) 23 24 25 26 27 28 29 30 31EXHIBIT 26.1 Year Month Amount Mountain Village Clinic: Billings Forecast 2017 November $300,000 December 500,000 2018 January 700,000 February 900,000 March 600,000 April 300,000 May 200,000 June 400,000 July 500,000 August 400,000