I am really on on this question and am not sure where to begin. Even if it could just be started to give me an idea of how to start it with some tips that would be great! I know it might be a little l
I am really on on this question and am not sure where to begin. Even if it could just be started to give me an idea of how to start it with some tips that would be great! I know it might be a little lengthy so the whole thing doesn’t have to be done I just need to know where to start.
Some information is as follows –>
Revenues are based on the number of patients seen.
The average revenue per patient office visit is $122.
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.
The clinic is planning to invest $105,000 in new equipment in May.
The clinic has a balance of $25,000 in cash as of Jan 1.
Income receipts are composed of:
Sales for some of the months in the prior year were:
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
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
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.
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
Finally, the clinic is thinking about increasing their pricing to $145 per patient visit beginning in
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).
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
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.
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
Expected 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.
Depreciation on equipment is $1,000 per month.
This has been included in the Other fixed operating
The 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.
The 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.
Cash operating expenses are paid in the same month they are incurred.
However, outside lab fees are
paid the following month.
% of total
No. of months after
service for receipt