Question

Paymore Products places orders for goods equal to 75% of its sales forecast in the next quarter which has been provided in the below table.

	Quarter in Coming Year					Following Year
	First	Second	Third	Fourth		First Quarter
Sales forecast	$388	$360	$342	$390		$390

Paymore’s labor and administrative expenses are $71 per quarter and interest on long-term debt is $46 per quarter. Paymore’s cash balance at the start of the first quarter is $40 and its minimum acceptable cash balance is $30. Assume that Paymore can borrow up to $342 from a line of credit at an interest rate of 2% per quarter. On average, one-third of sales are collected in the quarter that they are sold, and two-thirds are collected in the following quarter. Assume that sales in the last quarter of the previous year were $342. Also, one third of the orders are paid for in the current month and then two thirds of the next quarter's orders are paid in advance. Prepare a short-term financing plan using the above table. (Leave no cells blank. Enter '0' when necessary. Negative amounts should be indicated by a minus sign. Do not round intermediate calculations. Enter your answers in millions of dollars rounded to 2 decimal places.)

	Quarter (figures in $ millions) First Second Third Fourth A. Cash requirements Cash required for operations Interest on bank loan Total cash required B. Cash raised in quarter Line of credit Total cash raised C. Repayments of bank loan D. Addition to cash balances E. Line of credit Beginning of quarter End of quarter

Answer 1

Here, the following assumption or pointers need to be made:

1. The orders are paid for 1/3 rd in the current month (I think this might be a mistake; it should be quarter) and 2/3rd of the next quarter's orders are paid in advance. That implies that the current quarter order were already paid in advance to the extent of 2/3rd of the amount and the balance 1/3rd will be paid in the current quarter.

2. The Line of Credit has an interest rate of 2 percent per quarter. We will be calculating the interest at the gross amount of credit taken (before interest) to simplify this. Further, if in one quarter the Line of Credit has been taken, it will be carried forward in the next quarter unless repaid.

3. With reference to point 1, we have no data for the sales forecast for the second quarter of Next Year. It is needed to calculate the amount of orders that will be placed in the First Quarter of next year. And after that the advance payment will be needed to be calculated for the orders to be made in the First quarter of next year. Assuming it is not available the date has been adjusted accordingly.

4. I have made the working using an excel file, and the final result of the data has also been made available.

Now, to calculate inflows. Inflow of cash will be two fold:

1. Receipts from the Sales. That will be 1/3rd received in the same quarter and 2/3rd received in the next quarter. Hence, for the First Quarter of current year, the sales receipts will be as follows:

1/3rd of Sales in Current Quarter (1st qtr of Current year) = 388/3 = 129.33

2/3rd of Sales in Last year 4th Quarter = 342 * 2/3 = 228

Hence, the total receipts will be = 129.33+228 = 357.33

2. Borrowing from Line of Credit. If there is deficit cash below the minimum acceptable level of 30, then there will be credit taken. The amount of credit will be calculated after adjusting for interest portion. Suppose there is deficit of 12. Then we'll need to take credit of 12.24. Hence, after calculating the interest @ 2% on the Credit amount the net deficit will be fulfilled.

i.e. 12.24 - (12.24*2%) = 12.24 - 0.24 = 12

The calculations have been made accordingly.

For the Outflow of cash i.e. payments. These will be as follows:

1. Recurring expenses per quarter. These will be the Labour and Administration expenses i.e. 71 per quarter and Interest on Long Term Debt i.e. 46 per quarter. Hence, total of 117 per quarter.

2. Payments for orders. Here order value would be 75% of the amount of sales forecast for the next quarter. Further the total payment will be the 1/3rd of the amount of order for the current quarter and the 2/3rd of the amount of the order for the next quarter. So for the First quarter of the current year, the payment will be

1/3rd of Order in current quarter i.e. (360*0.75)/3 = 90

2/3rd of order in next quarter i.e. (342*0.75)*2/3 = 171

Hence, the total payment in this quarter would be 90+171 = 261

3. Another outflow of cash would be the interest payment on the Line of Credit. This would be calculated at the Gross Amount of Credit Taken i.e. the deficit pending after the Receipts and Payments have been done.

Now, Short-Term Financing plan would be as follows:

	Quarter
(figures in $ millions)	First	Second	Third	Fourth
A. Cash requirements
Cash required for operations	-20.67	-18.83	-55.50	0.00
Interest on bank loan	-0.22	-0.61	-1.75	0.00
Total cash required	-20.88	-19.44	-57.25	0.00
B. Cash raised in quarter
Line of credit	10.88	19.44	57.25	0
Total cash raised	10.88	19.44	57.25	0
C. Repayments of bank loan	0.00	0.00	0.00	87.57
D. Addition to cash balances	-10.00	0.00	0.00	55.93
E. Line of credit	10.88	30.32	87.57	0
Beginning of quarter	40	30	30	30
End of quarter	30	30	30	86

Now my working file on the above data would be as follows:

			Current Year				Next year
	Last Year Qtr 3	Last yr qtr 4	First	Second	third	fourth	First qtr
Sales Forecast		342	388	360	342	390	390
Order in Qtr	256.5	291	270	256.5	292.5	292.5	0
Payments for order		277	261	280.5	292.5	97.5
Expenses per quarter		117	117	117	117	117
Receipts on Sales		114	357.33	378.67	354	358
	Opening Cash		40	30	30	30
	Receipts
	From Sale		357.33	378.67	354.00	358.00
	Loan		10.88	19.44	57.25
		Total Inflow	368.21	398.11	411.25	358.00
	Expenses		378	397.5	409.5	214.5
	Interest		0.22	0.61	1.75	0.00
	Repayment of Credit		0.00	0.00	0.00	87.57
		Total Outflow	378.22	398.11	411.25	302.07
	Net Cash		30.00	30.00	30.00	85.93
Check	Min Cash		30	30	30	30
	Increase / Decrease in Cash		-10.00	0.00	0.00	55.93
	LC Opening		0	10.88	30.32	87.57
	Taken during Quarter		10.88	19.44	57.25	0
	Repaid during Quarter		0	0	0	87.57
	LC Closing		10.88	30.32	87.57	0
	Interest on LC		0.2176	0.6064	1.7514	0
	Deficit		0.00	0.00	0.00	-55.93