Assignment 1: LASA 2—Cash Management and Budgeting

The new owner of Bob’s Boats, Bob, just purchased the business and discovered that the prior owner did not have a plan for managing the current assets of the company. Bob has decided that the place to start is with a cash budget, using the template he used in his prior position.

Bob has requested that you to complete the cash budget given the data that he has already retrieved from the company’s records and from his own analysis.
Table-1:
Sales Forecast for the next 9 Months
Month
Sales
January
$ 162,000.00
February
$ 168,000.00
March
$ 324,000.00
April
$ 485,000.00
May
$ 648,000.00
June
$ 325,000.00
July
$ 325,000.00
August
$ 80,000.00
September
$ 162,000.00

Collections on receivables have historically been collected as follows and this pattern is expected to continue:
12% in the Month of Sale
60% in the month following the Sale
28% in the Second Month after the Sale
Bob pays for the labor and materials in the month after they are incurred. Bob has also compiled the following information on expenses for the upcoming nine month period:
Table-2:
Month
Labor and Materials Expense
Administrative Salaries
Depreciation Charges
Income Taxes
Lease Payments
Miscellaneous Expenses
January
$ 80,000.00
$ 25,000.00
$ 34,500.00
-
$ 8,500.00
$ 3,000.00
February
$ 80,000.00
$ 25,000.00
$ 34,500.00
-
$ 8,500.00
$ 3,000.00
March
$ 114,000.00
$ 25,000.00
$ 34,500.00
-
$ 8,500.00
$ 3,000.00
April
$ 794,000.00
$ 25,000.00
$ 34,500.00
-
$ 8,500.00
$ 3,000.00
May
$ 275,000.00
$ 25,000.00
$ 34,500.00
$ 58,500.00
$ 8,500.00
$ 3,000.00
June
$ 210,500.00
$ 25,000.00
$ 34,500.00
-
$ 8,500.00
$ 3,000.00
July
$ 146,000.00
$ 25,000.00
$ 34,500.00
-
$ 8,500.00
$ 3,000.00
August
$ 80,000.00
$ 25,000.00
$ 34,500.00
$ 58,500.00
$ 8,500.00
$ 3,000.00
September
$ 81,000.00
$ 25,000.00
$ 34,500.00
-
$ 8,500.00
$ 3,000.00
Bob has also identified a cash payment of $175,000 upon completion of a new boat dock in July. As of March 1st, the company now has $125,000 in cash on hand and would like to maintain a minimum cash balance of $85,000 at all times.
Required:

Prepare a Schedule of Collections and Payments for the six month period beginning in March, using the template provided and the data above.

Prepare a Schedule of Cash Gains and Losses by month for the six month period beginning in March, using the template provided and the data above.

Prepare a Schedule of Cash Surplus or Shortages by month for the six month period beginning in March, using the template provided and the data above.

Prepare a schedule of Net Cash Flow by month for the six month period beginning in March, using the template provided and the data above indicating the timing and amounts that must be borrowed or the amounts that can be re-invested or used in the firm.

Based on the information in the reports you constructed above would you recommend any changes in the credit policy of the firm? If so, what would they be? Explain your answers.

Mary has been working for a university for almost 25 years and is now approaching retirement. She wants to address several financial issues before her retirement and has asked you to help her resolve the situations below. Her assignment to you is to provide a 4-5 page report, addressing each of the following issues separately. You are to show all your calculations and provide a detailed explanation for each issue.

Issue A:
For the last 19 years, Mary has been depositing $500 in her savings account , which has earned 5% per year, compounded annually and is expected to continue paying that amount. Mary will make one more $500 deposit one year from today. If Mary closes the account right after she makes the last deposit, how much will this account be worth at that time?

Issue B:
Mary has been working at the university for 25 years, with an excellent record of service. As a result, the board wants to reward her with a bonus to her retirement package. They are offering her $75,000 a year for 20 years, starting one year from her retirement date and each year for 19 years after that date. Mary would prefer a one-time payment the day after she retires. What would this amount be if the appropriate interest rate is 7%?

Issue C:
Mary’s replacement is unexpectedly hired away by another school, and Mary is asked to stay in her position for another three years. The board assumes the bonus should stay the same, but Mary knows the present value of her bonus will change. What would be the present value of her deferred annuity?

Issue D:
Mary wants to help pay for her granddaughter Beth’s education. She has decided to pay for half of the tuition costs at State University, which are now $11,000 per year. Tuition is expected to increase at a rate of 7% per year into the foreseeable future. Beth just had her 12th birthday. Beth plans to start college on her 18th birthday and finish in four years. Mary will make a deposit today and continue making deposits each year until Beth starts college. The account will earn 4% interest, compounded annually. How much must Mary’s deposits be each year in order to pay half of Beth’s tuition at the beginning of each school each year?

Mary has been working for a university for almost 25 years and is now approaching retirement. She wants to address several financial issues before her retirement and has asked you to help her resolve the situations below. Her assignment to you is to provide a 4-5 page report, addressing each of the following issues separately. You are to show all your calculations and provide a detailed explanation for each issue.

To complete the assigned tasks, follow these steps:

1. To start, you will need to calculate the collections and payments for each month. Begin by using the sales forecast given in Table-1 and the historical collection pattern provided. In the "Collections" section of your schedule template, multiply the sales forecast for each month by the corresponding collection percentages for that month.

2. For example, for January, the sales forecast is $162,000. Apply the collection percentages: 12% in the month of sale, 60% in the month following the sale, and 28% in the second month after the sale. Calculate the collections by multiplying the sales forecast by the corresponding percentages.

3. Next, move on to calculating the payments for each month. Refer to Table-2 for the labor and materials expenses, administrative salaries, depreciation charges, income taxes, lease payments, and miscellaneous expenses. Fill in the payment amounts for each category in the corresponding month.

4. Additionally, consider the cash payment of $175,000 for the new boat dock in July. Include this amount as a payment in the appropriate month.

5. After completing the collections and payments for each month, you can proceed to the next task: preparing a Schedule of Cash Gains and Losses. This schedule will show the net change in cash for each month. Begin with the cash balance on hand on March 1st ($125,000) and adjust it based on the collections and payments calculated in step 1 and step 3.

6. To prepare a Schedule of Cash Surplus or Shortages, use the cash gains and losses calculated in the previous step. Subtract any cash shortages from the beginning cash balance to determine whether there is a surplus or shortage for each month.

7. Finally, prepare a Schedule of Net Cash Flow by month. Start with the beginning cash balance and include any cash surplus or shortage from the previous step. This schedule will show the timing and amounts that need to be borrowed or can be re-invested or used in the firm.

8. Based on the information in the reports you have constructed, analyze the cash flow situation of the company. Consider the cash surplus or shortage for each month. Assess whether the company's credit policy needs any changes. If the cash flow reports indicate consistent shortages, the firm may need to reconsider its credit policy, such as tightening credit terms or implementing stricter collection procedures. Conversely, if there is a consistent surplus, the firm may consider relaxing credit terms to encourage more sales.

Make sure to fill in the data and complete the templates provided in the assignment instructions accurately to obtain the required schedules and reports.