Question

Consider the following third-quarter budget data for TAP & Brothers:

TAP & Brothers Third-Quarter Budget Data

	July	August	September
Credit Sales	258941	265299	284380
Credit Purchases	97804	116018	135024
Wages, Taxes, and Expenses	26612	31353	33454
Interest	7193	7571	8062
Equipment Purchases	54213	61494	0

The company predicts that 4% of its credit sales will never be collected, 30% of its sales will be collected in the month of the sale, and the remaining 66% will be collected in the following month. Credit purchases will be paid in the month following the purchase.

In June, credit sales were $138541, and credit purchases were $102763

July’s beginning cash is $184593

If TAP maintains a policy of always keeping a minimum cash balance of $75,000 as a buffer against uncertainty and forecasting errors, what is the cash surplus/deficit at the end of the quarter (i.e., end of September)? (Answer surplus as a positive number or deficit as a negative number. Round answer to 0 decimal places. Do not round intermediate calculations)

Answer 1

For computation of cash surplus/deficit at the end of the quarter (Sep end), we need to calculate monthly cash movement i.e. net cash (total cash inflow - total cash outflow) generated every month. The total cash inflow can be calculated by aggregating previous month's collection (66% of last month's credit sales) and current month's collection (30% of current month's credit sales). The total cash outflow can be calculated by taking the sum of previous month's credit purchases and current month's expenses (Wages, taxes and expenses, interest, equipment purchases).

Ending cash in a month is the sum of beginning cash in that month and net cash generated during that month. There is also a validation required that ending balance should be more than $75,000. The following table provides the required data:

Particulars	July	August	September
Credit Sales	2,58,941.00	2,65,299.00	2,84,380.00
Credit Purchases	97,804.00	1,16,018.00	1,35,024.00
Wages, Taxes and Expenses	26,612.00	31,353.00	33,454.00
Interest	7,193.00	7,571.00	8,062.00
Equipment Purchases	54,213.00	61,494.00	-
Total Cash Outflow	1,90,781.00	1,98,222.00	1,57,534.00
Previous month collection	91,437.06	1,70,901.06	1,75,097.34
Current month collection	77,682.30	79,589.70	85,314.00
Total Cash Inflow	1,69,119.36	2,50,490.76	2,60,411.34
Net Cash	-21,661.64	52,268.76	1,02,877.34
Beginning Cash	1,84,593.00	1,62,931.36	2,15,200.12
Ending Cash	1,62,931.36	2,15,200.12	3,18,077.46
Ending Cash > 75,000	TRUE	TRUE	TRUE