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.

ATTACHMENT PREVIEW

Download attachment

qattachments_79b2a58930ffc48ea5a80881a68f2d80ff270670.xlsx, Information
Some information is as follows –>
1
Revenues are based on the number of patients seen.
The average revenue per patient office visit is $122.
2
3
Staff salaries are $32,000 per month.
4
The cost of outside lab work averages $28 per patient office visit.
5
Rent on the building is $2,500 per month.
6
Other fixed operating expenses such as insurance, utilities, etc are $2,600 per month.
7
8
The clinic is planning to invest $105,000 in new equipment in May.
9
10
The clinic has a balance of $25,000 in cash as of Jan 1.
11
12
13
Income receipts are composed of:
Private insurance
40%
same month
Cash patients
10%
same month
Medicare
25%
1
Medicaid
25%
2
14
Sales for some of the months in the prior year were:
October
47,500
November
49,400
December
51,376
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.
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
expenses above.
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
revenue
No. of months after
service for receipt

Leave a Comment