| CASE 8 | | Student Version | | | | Copyright 2010 |
| 8/24/09 | | | | | | by FACHE |
| | ALPINE VILLAGE CLINIC |
| | Cash Budgeting |
| This case demonstrates the use of a cash budget for analyzing a business's cash flows and |
| financing needs over time. The case includes both monthly and daily budgets. |
| The worksheet has a single input section, but it has separate models for the monthly and the |
| daily cash budgets. Changes in input data will flow through to both models and will be reflected |
| in the output. Note that the model extends out to Column M. |
| The model consists of a complete base case analysis--no changes need to be made to |
| the existing MODEL-GENERATED DATA section. However, all values in the student version | | | | | | | | | | Please complete questions 1-10 below and all calculations in red. Thanks! |
| INPUT DATA section have been replaced with zeros. Thus, students must determine | | | | | | | | | | I have attached the case study, but we do not have to do any of the questions |
| the appropriate input values and enter them into the model. These cells are colored red. | | | | | | | | | | on the case study. Just the ones below. Thank you |
| When this is done, any error cells will be corrected and the base case solution will appear. |
| Note that the instructor model includes a separate spreadsheet (CASE8 WITH INTEREST) |
| that includes interest paid and received. Students will have to create such a model, as well |
| as create their own graphics(charts) as needed to present their results. |
| INPUT DATA: | | | | | KEY OUTPUT: |
| Projected Billings ($): | | Projected Cost Data: | | | | Net |
| | | | | | | Cash Flow |
| | 2009 | Variable costs: | | | Jan | $0 |
| November | 0 | Supplies as a % of billings | | 0.00% | Feb | $0 |
| December | 0 | % paid before use: | | | Mar | $0 |
| | 2010 | 2 months prior | | 0.00% | Apr | $0 |
| January | 0 | 1 month prior | | 0.00% | May | $0 |
| February | 0 | | | | Jun | $0 |
| March | 0 | Fixed Costs: |
| April | 0 | Monthly clinical labor costs: | | | | Cum (Loan) |
| May | 0 | High season | | $0 | | or Surplus |
| June | 0 | Low season | | $0 | Jan | $0 |
| July | 0 | | | | Feb | $0 |
| August | 0 | Other monthly costs: | | | Mar | $0 |
| | | Gen/admin expense | | $0 | Apr | $0 |
| Collections Data: | | Lease payment | | $0 | May | $0 |
| | | Misc expense | | $0 | Jun | $0 |
| % from patients | 0.00% |
| % coll in 1 month | 0.00% | Semi-annual costs: |
| % coll in 2 months | 0.00% | Loan amortization | | $0 |
| Billings as a % | | One-time costs: |
| of forecast* | 0.00% | New equipment | | $0 |
| Cash Balance Data: |
| | | *This input is used to create scenarios in which |
| Target balance | $0 | actual collections are less than forecasted. To |
| Beginning cash | $0 | start, use 100% as the value. |
| 1. Construct a monthly cash budget for the clinic for the period January through June 2010. What is the maximum monthly loss during the six-month planning period? What is the maximum cumulative borrowing balance? (For purposes of this question, disregard any interest payments on short-term bank loans or interest received from investing surplus funds.) |
| 2. The monthly cash budget you have prepared assumes that all cash flows occur on the same day each month. Suppose the clinic's outflows tend to cluster at the beginning of the month, while collections tend to be heaviest toward the end of each month. |
| a. How would this imbalance affect the validity of the monthly budget? |
| b. What could be done to correct any inaccuracies that might occur? |
| 3. Construct the clinic's daily cash budget for January. Does it indicate the same maximum borrowing requirement for the month as does the monthly budget? Does the end-of-January borrowing requirement in the daily budget match the end-of-January borrowing requirement in the monthly budget? If not, explain the difference. |
| 4. Should seasonal variations be incorporated into the clinic's target cash balance? In other words, should the balance be higher during months when cash needs are greater? |
| 5. The only receipts shown on the clinic's cash budget are collections. What other types of cash inflows could occur? |
| 6. Consider the interest paid on short-term borrowings and earned on short-term investments. Modify the monthly portion of the model to include these cash flows. Do these flows have a significant impact on estimated borrowing requirements? |
| 7. The clinic's cash management policy is to invest any surplus funds in marketable securities. |
| a. Can you suggest an investment policy that would provide liquidity and safety yet offer the clinic a reasonable return on its investment? Specifically, describe the types of securities, the desired maturities, the espected returns, and the risks that would be involved. |
| b. Would your suggestions be the same for a business whose cash balances were projected to be in the millions of dollars as opposed to thousands of dollars? |
| 8. What would be the impact on the monthly net cash flows if actual billings from November 2009 to June 2010 were 20 percent below the forecasted amounts? What if they were 50 percent below the forecasted amounts? In your answers, assume that purchases and labor costs, as well as other expenses, cannot be adjusted downward during this period even though realized volume was below that forecasted. |
| 9. Suppose the clinic's third-party payers changed their payment patterns and began paying as follows: 10 percent in the month of sale, 20 percent in the following month, and 70 percent in the second month versus the old 20-20-60 pattern. How large a credit line would the clinic require? |
| 10. On the basis of your analyses, how large a credit line would you recommend that the clinic seek from First Bank? |
[email protected]
book2.xlsx
