Question

Alpha-Tech, a rapidly growing distributor of electronic components, is formulating its plans for 20x5. Carol Jones, the firm's marketing director, has completed the following sales forecast. ALPHA-TECH 20x5 Forecasted Sales (in thousands) Month Sales January $10,500 February 11,500 March 10,500 April 13,000 May 14,000 June 15,500 July 16,500 August 16,500 September 17,500 October 17,500 November 16,500 December 18,500 Phillip Smith, an accountant in the Planning and Budgeting Department, is responsible for preparing the cash flow projection. The following information will be used in preparing the cash flow projection. • Alpha-Tech's excellent record in accounts receivable collection is expected to continue. Sixty percent of billings are collected the month after the sale, and the remaining 40 percent two months after. The purchase of electronic components is Alpha-Tech's largest expenditure, and each month's cost of goods sold is estimated to be 40 percent of sales. Seventy percent of the parts are received by Alpha-Tech one month prior to sale, and 30 percent are received during the month of sale. Historically, 75 percent of accounts payable has been paid one month after receipt of the purchased components, and the remaining 25 percent has been paid two months after receipt. Hourly wages and fringe benefits, estimated to be 30 percent of the current month's sales, are paid in the month incurred. • General and administrative expenses are projected to be $16,020,000 for the year. The breakdown of these expenses is presented in the following schedule. All cash expenditures are paid uniformly throughout the year, except the property taxes, which are paid in four equal installments at the end of each quarter.

Answer 1

Particulars	April	May	June
Beginning Balance	580	580	1381.66
Collections
Feb Sales	4600
March Sales	6300	4200
April Sales		7800	5200
May Sales			8400
Total Receipts	10900	12000	13600
Total Cash Available	11480	12580	14981.66
Disbursements
Payable	4755	5335	5885
Wages	3900	4200	4650
G&A	1214.17	1214.17	1214.16
Prop Taxes			362.5
Inc Taxes	1480
Total Disbursements	11349.17	10749.17	12111.66
Cash Balance	130.83	1830.83	2870
Cash Borrowed	449.17
Cash repaid	-	449.17
Ending Balance	580	1381.66	2870

Working

Particulars	Sales	COGS	Payables	Payment	Wages
February	11500	4600	4320
March	10500	4200	4900
April	13000	5200	5480	4755	3900
May	14000	5600	6020	5335	4200
June	15500	6200	6480	5885	4650
July	16500	6600

Explanation taking base of April

1. Sales is received 60% in next month and 40% after 2 months

For April, 60% of March Sales and 40% of Feb Sales

2. COGS is 40% of Sales. 70% of goods are purchased one month in advance and 30% in month of Sales

i.e for April, 70% of April COGS and 30% of May COGS

Payment is made 25% in next month and 75% after 20 months

For April, Payment will be 25% of March Payables and 75% of Feb Payables

3. Wages is 30% of the given month sales i.e April Wages is 30% of April Sales

4. G&A is 14570 (excluding Prop Taxes) for the full year i.e 1214.16 for each month

5. Prop Taxes 1450/4 i.e 362.5 paid in June

6. Income Taxes 3700*0.4% = 1480 paid in April

7. Amount is borrowed to keep minimum balance of 580 at end of April'

Excess money in May is repaid in MAy.