ACCT 311, Fall, 2013      
Budget Case      
For this case, you are to prepare a cash budget for the Mountain View Medical Center, a medical clinic that has been in operation for 8 years. 
The clinic needs a monthly cash budget for the next year.  Their year is a calendar year (Jan to Dec.) 
The clinic is concerned that they will have to borrow funds to cover the planned capital investments. This is acceptable as they have a line of credit, but they have to know in advance. 
The clinic has to maintain a minimum cash balance of $25,000 to cover variances in cash flows from day to day.  Assume that any borrowings are made on the last day of each month and in increments of $5,000.  For example, if the clinic is projecting a cash shortfall of $18,000 in April, you will need to show additional borrowings of $20,000 in the month of April. 
If the clinic has a cash balance over $75,000 at the end of a month, they can pay down their loan balance in increments of $5,000.  The repayments will be made on the last day of the month.  For example, if the clinic is projecting a positive cash balance of $92,000 at the end of April, they can repay 65,000 in April.  Be sure that repayments do not reduce the cash balance below their minimum.  
Finally, the clinic is thinking about increasing their pricing to $145 per patient visit beginning in August.  They do not believe that the price increase will impact the number of patient visits. Also, they expect that starting with Jan services Medicaid will reduce their reimbursement to 90% and pay in the 3rd month after service. You will need to prepare 2 Cash Budgets:  one with the initial pricing and a second one with the price increase and the delay in Medicaid payments. Make a copy of your initial cash budget worksheet in this workbook.  Your final workbook will have 3 worksheets: Information, Cash Budget, and Cash Budget(2). 
This project is required to be done on excel or other spreadsheet software (eg OpenOffice).  The file must be submitted in a Windows format (.xls or .xlsx) to receive your grade. 
In the space below the projections, include any comments or suggestions for management that you have. 
Points will be given for accuracy and for the use of formulas in excel.  If all calculations are accurate, but there is no or limited use of formulas for the computations, the maximum credit is 85 points.  If all calculations are accurate and there is good use of formulas, then 110 points can be earned. 
Points will be calculated as 50 points for accuracy on the basic model, 25 points for the second version, 10 points for comments or suggesions, and 25 points for good use of excel formula capabilities. 
Some information is as follows -->     
1Revenues are based on the number of patients seen.  The average revenue per patient office visit is $122.
2Expected patient volumes are 425 in January.  The volume will increaseby 5% per month from Feb to April, but falls in May, June, and July by 7% each month.  In August and through December, volume will increase by 3.5% per month.
3Staff salaries are $32,000 per month.
4The cost of outside lab work averages $28 per patient office visit.
5Rent on the building is $2,500 per month.
6Other fixed operating expenses such as insurance, utilities, etc are $2,600 per month.
7Depreciation on equipment is $1,000 per month.  This has been included in the Other fixed operating expenses above.
8The clinic is planning to invest $105,000 in new equipment in May.
9The clinic is also planning to implement an electronic medical records system.  Costs will be $45,000 in Feb and $18,000 each in Oct, Nov, and Dec.
10The clinic has a balance of $25,000 in cash as of Jan 1.
11The clinic has an outstanding loan.  The loan balance is $250,000.  Interest is at 6% (per year) and is paid at the end of each calendar quarter.  In addition, a principal payment of $20,000 is required in Dec.
12Cash operating expenses are paid in the same month they are incurred.  However, outside lab fees are paid the following month.
13Income receipts are composed of:
 % of total revenueNo. of months after service for receipt    
Private insurance40%same month    
Cash patients10%same month    
14Sales for some of the months in the prior year were:   
October       47,500     
November       49,400     
December       51,376     
    • 7 years ago

    Purchase the answer to view it

    • attachment