Interested in a PLAGIARISM-FREE paper based on these particular instructions?...with 100% confidentiality?

Order Now

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 –> Revenues 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 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. 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 operating expenses 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 in Feb and $18,000 each in Oct, Nov, and Dec. 10 The clinic has a balance of $25,000 in cash as of Jan 1. 9 11 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. 12 Cash operating expenses are paid in the same month they are incurred. However, outside lab fees are paid the following month. 13 Income receipts are composed of: % of total No. of months after revenue service for receipt 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 qattachments_b88810d4552e5a8a231b7a1104c008be3a4d40fe.xls, Information Mountain View Medical Center Oct-13 Revenue Projection: No of patients: Rate per patient visit Projected revenue

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 –>

Revenues 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 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.
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 operating
expenses 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 in
Feb and $18,000 each in Oct, Nov, and Dec.
10 The clinic has a balance of $25,000 in cash as of Jan 1.
9

11 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.
12 Cash operating expenses are paid in the same month they are incurred. However, outside lab fees are
paid the following month.
13 Income receipts are composed of:
% of total No. of months after
revenue
service for receipt
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

qattachments_b88810d4552e5a8a231b7a1104c008be3a4d40fe.xls, Information

Mountain View Medical Center
Oct-13

Revenue Projection:
No of patients:
Rate per patient visit
Projected revenue

Interested in a PLAGIARISM-FREE paper based on these particular instructions?...with 100% confidentiality?

Order Now