ACCOUNTING – Mountain View Medical Center……………

For this case, you are to prepare a cash budget for the Mountain View Medical Center, a medicalclinic 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 toDec.)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 fromday to day. Assume that any borrowings are made on the last day of each month and in incrementsof $5,000. For example, if the clinic is projecting a cash shortfall of $18,000 in April, you will need toshow 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 loanbalance in increments of $5,000. The repayments will be made on the last day of the month. Forexample, if the clinic is projecting a positive cash balance of $92,000 at the end of April, they canrepay 65,000 in April. Be sure that repayments do not reduce the cash balance below theirminimum.Finally, the clinic is thinking about increasing their pricing to $145 per patient visit beginning inAugust. 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 payin the 3rd month after service. You will need to prepare 2 Cash Budgets: one with the initial pricingand a second one with the price increase and the delay in Medicaid payments. Make a copy of yourinitial 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 filemust 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 youhave.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 allcalculations 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 secondversion, 10 points for comments or suggesions, and 25 points for good use of excel formulacapabilities.Some information is as follows –>12345678Revenues are based on the number of patients seen. The average revenue per patient office visit is $122.Expected patient volumes are 425 in January. The volume will increaseby 5% per month from Feb toApril, but falls in May, June, and July by 7% each month. In August and through December, volume willincrease by 3.5% per month.Staff salaries are $32,000 per month.The cost of outside lab work averages $28 per patient office visit.Rent on the building is $2,500 per month.Other fixed operating expenses such as insurance, utilities, etc are $2,600 per month.Depreciation on equipment is $1,000 per month. This has been included in the Other fixed operatingexpenses above.The clinic is planning to invest $105,000 in new equipment in May.The clinic is also planning to implement an electronic medical records system. Costs will be $45,000 inFeb and $18,000 each in Oct, Nov, and Dec.10 The clinic has a balance of $25,000 in cash as of Jan 1.911 The clinic has an outstanding loan. The loan balance is $250,000. Interest is at 6% (per year) and is paidat the end of each calendar quarter. In addition, a principal payment of $20,000 is required in Dec.12 Cash operating expenses are paid in the same month they are incurred. However, outside lab fees arepaid the following month.13 Income receipts are composed of:% of total No. of months afterrevenueservice for receiptPrivate insurance40%same monthCash patients10%same monthMedicare25%1Medicaid25%214 Sales for some of the months in the prior year were:October47,500November49,400December51,376