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?

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 31