Excel Lab 6 – Creating a Cash Budget
In planning for 2019, you need to build a cash budget for the upcoming year to help your home health agency (Grand River Home Health) model its cash needs to be given several scenarios. Prepare a cash budget for your agency for the next eight months. Your office typically likes to maintain a $100,000 minimum cash balance during each month required for working capital. Coming off a good year, you have $125,000 in cash on hand. Based on last year’s billings, you expect the following revenues:
January $700,000 February $700,000 March $800,000 April $800,000 May $900,000 June $1,000,000 July $1,200,000 August $1,200,000
Grand River Home Health collects 30% of its revenue in the month billing occurred, 30% in the next month, and 25% in the following month. The firm fails to collect 15% of its revenue because of either bad debt or contractual allowances.
Expense budget relationships are presented in the following:
Payroll = $50,000 per month plus .45 X revenues
Supplies = .10 x revenues
Rent = $25,000 per month
Debt Service – $150,000 in July
Capital Expenditures – $75,000 in June
Eighty percent of payroll expenses are paid in the month this expense is incurred, and 20% is paid the following month. All other items are paid in the month reported. Determine which months Grand River will be able to invest surplus funds and during which months it might need to borrow.
1
Also considering the following:
Grand River’s cash management policy is to invest any surplus funds into
marketable securities.
1. Can you suggest an investment policy that would provide liquidity and
safety, yet offer the clinic a reasonable return on its investment?
Specifically, describe the types of securities, the desired maturities
expected returns, and the risks involved.
Developing a cash budget and making projections about cash needs can be
influenced by minor adjustments to the calculations, also called sensitivity
analysis.
2. What would be the impact on the monthly net cash flows is billings
during the 8 month period were 2.5% below forecasted amounts?
Determine which months Grand River will be able to invest surplus
funds and during which months it might need to borrow. In addition,
your labor costs have risen to $50k per month + .50 X revenues.
2
Excel Lab 6 to Complete on Your Own
In Excel Lab 6 you will partially develop a cash budget similar to the
examples I worked on in the previous videos. In healthcare, we do not always
get paid in the same month as we provide services. In this instance, you will
have two tasks:
1. Estimate how much we are likely to receive in billing each month, and
2. Estimate payroll disbursements
You have just one scenario to complete. Complete the tables tab located in
your spreadsheet entitled Scenario 1.
Scenario 1 Tab
Use the following assumptions to complete the table.
Revenue Assumptions
 The expected revenues or billings are listed for each month (Jan –
Sept) for 2017.
 We typically receive the cash from our billings over three months.
We expect to collect 40% of our revenue in the month in which the
billing occurred, 35% of our revenue in the following month (2nd
month) and 15% in the 3rd month. We fail to collect 10% of our
billings due to bad debt.
Payroll Assumptions
 Our payroll assumptions are $55,000 per month + 47% of gross
revenues
 Seventy-five percent of payroll expenses are paid in the month this
expense is incurred, and 25% is paid the following month.
Use the table on the following page to help you complete the exercise and
submit the following responses to Moodle.
3
When entering your responses:
 Do not include the $ in your responses
 Round your responses to the nearest whole dollar. Do not include the
cents
 Do not use comma separators in your response
4
 Scenario 1
5

Order any type of Service We Provide

We are the best assignment service that can satisfy student’s demands in different scientific fields. We perform tasks of any kind. Our specialists provide diverse custom assignment writing services to students from all over the world every day. You can contact us for assistance with:

• Essays of any type (application, scholarship, argumentative, personal, informative, persuasive, compare and contrast, narrative, analytical, cause and effect, critical, process, descriptive, expository essays);
• Homework,
• Assignments,
• Reviews of all types (for a book, an article, or a film),
• Reports,
• Annotated bibliography,
• Projects,
• Thesis,
• Term papers,
• Presentations,
• Lab works,
• Research papers,
• Speeches,
• Critical thinking,
• Capstone projects;
• Business plan;
• Coursework’s;
• Dissertations.

The list is far from complete!

Representatives of our student assignment service are connoisseurs of the peculiarities of presentation in regards to academics. Your college assignment will never turn out an untoward surprise! Whether you need research for high school, an undergraduate program, or a Master’s or Doctoral degree, you will get exactly what you are looking for to sound smart and well-informed.

If you are pressed for time, request our experts!

We are your academic saver. It is a perfect solution for people who need academic help but has no professionals nearby to provide it. Just redirect your home tasks to us and forget about any educational issues. With us, you will get 100% plagiarism-free content delivered on time by an experienced specialist in a particular field.

Main Advantages of Collaboration with Us

When you order professional assignments here, you will get:

Original custom papers. We value your academic reputation. Just as well, we value the years of thorough work on our reputation for reliability and never compromise the originality of delivered papers. We will never endanger both. Every custom assignment is written from zero – the only possible first stage of work on the order is research. We apply the latest plagiary checking tools on the final stage, so plagiarism has no chance to emerge in your college assignment writing.

Control over the order completion. To make the process of collaboration comfortable and efficient, we offer our clients to choose the writer themselves taking into account all the requirements and the budget. For you to save nerves, having entrusted your fateful task to some qualified “stranger”, we enable communication with the assigned writer in the process of order completion.

Affordable assistance. We are eager to contribute to the academic achievements of students in need of help with college assignments. We are eager to help you start investing in your career growth today. That is why our service is so affordable. We don’t charge to pay for the latest marketing tricks or advertisements – we choose the best experts in the labor market who write great custom papers in any area, level of complexity, and time frame. Our client’s grateful feedback is a top advertising trick.

On-time delivery. The quality of a paper is only valued when it is delivered within the determined time frame – you will get your custom assignment writing service in time and have a couple of days to revise it and ask for changes in case they are needed. And you will still have time to learn the material.

Protection. We value your trust and take all measures needed to keep your private and banking details safe. We offer only well-tested payment methods. Due to the system of encryption and protected servers, no third party can have access to your data.

Custom support 24/7. We do our best to make the process of collaboration comfortable for the customer. Our “write my assignment” help is always here for you to provide a solution to your problems, give you a prompt answer to any question, and offer clarification as to any issue related to the services. Feel free to contact us anytime!

If you feel that your dissatisfaction with student life is growing every day and you just feel exhausted, don’t hesitate to change the situation for the better today. Order your first assignment from reliable custom assignment services today to evaluate the advantages.

Having seen the difference once, you won’t want to go back to your previous lifestyle. You deserve to find fulfilment in other spheres of your life, have hobbies, spend time with close people but continue making progress. It is possible when you have a professional helper. Don’t put off your life for someday after graduation!

Order your paper now!