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		$	6,500
February			7,500
March			6,500
April			9,000
May			10,000
June			11,500
July			12,500
August			12,500
September			13,500
October			13,500
November			12,500
December			14,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. Seventy percent of billings are collected the month after the sale, and the remaining 30 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 30 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, 70 percent of accounts payable has been paid one month after receipt of the purchased components, and the remaining 30 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 $15,670,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.

  20x5 Forecasted General and Administrative Costs
  (in thousands)
  Salaries and fringe benefits	$	3,000
  Promotion		3,500
  Property taxes		1,330
  Insurance		2,830
  Utilities		1,500
  Depreciation		3,510
  Total	$	15,670

• Income-tax payments are made at the beginning of each calendar quarter based on the income of the prior quarter. Alpha-Tech is subject to an income-tax rate of 40 percent. Alpha-Tech’s operating income for the first quarter of 20x5 is projected to be $3,000,000. The company pays 100 percent of the estimated tax payment.
• Alpha-Tech maintains a minimum cash balance of $510,000. If the cash balance is less than $510,000 at the end of each month, the company borrows amounts necessary to maintain this balance. All amounts borrowed are repaid out of the subsequent positive cash flow. The projected April 1, 20x5, opening balance is $510,000.
• Alpha-Tech has no short-term debt as of April 1, 20x5.
• Alpha-Tech uses a calendar year for both financial reporting and tax purposes.

Required:

1. Prepare a cash budget for Alpha-Tech by month for the second quarter of 20x5. For simplicity, ignore any interest expense associated with borrowing. (Negative amounts should be indicated by a minus sign.)

2. ALPHA-TECH
   Cash Budget
   For the Second Quarter of 20x5
   	April	May	June
   Beginning balance
   Collections:
   February sales
   March sales
   April sales
   May sales
   Total receipts	$0	$0	$0
   Total cash available	$0	$0	$0
   Disbursements:
   Accounts payable
   Wages
   General and administrative
   Property taxes
   Income taxes
   Total disbursements	$0	$0	$0
   Cash balance	$0	$0	$0
   Cash borrowed
   Cash repaid
   Ending balance	$0	$0	$0

   This is a numeric cell, so please enter numbers only.

Answer 1

ALPHA-TECH
Cash Budget
For the Second Quarter of 20x5
	April	May	June
Beginning balance(A)	5,10,000.00	5,10,000.00	12,40,500.00
Collections:(Refer Working Note 1)
January sales	19,50,000.00	-	-
February sales	-	22,50,000.00	-
March sales	45,50,000.00	-	19,50,000.00
April sales	-	63,00,000.00	-
May sales	-	-	70,00,000.00
Total receipts(B)	65,00,000.00	85,50,000.00	89,50,000.00
Total cash available(C=A+B)	70,10,000.00	90,60,000.00	1,01,90,500.00
Disbursements:
Accounts payable	23,80,500.00	26,49,000.00	30,63,000.00
Working	Refer Working Note 2
Wages	27,00,000.00	30,00,000.00	34,50,000.00
Working	9000000*30/100	10000000*30/100	11500000*30/100
General and administrative	11,95,000.00	11,95,000.00	11,95,000.00
Working	(3500000+2830000+1500000+3510000+3000000)/12	(3500000+2830000+1500000+3510000+3000000)/12	(3500000+2830000+1500000+3510000+3000000)/12
Property taxes	-	-	3,32,500.00
Working			1330000/4
Income taxes	12,00,000.00
Working	3000000*0.4
Total disbursements(D)	74,75,500.00	68,44,000.00	80,40,500.00
Cash balance(E=C-D)	-4,65,500.00	22,16,000.00	21,50,000.00
Cash borrowed(F=510000-E)	9,75,500.00	-	-
Cash repaid(G)	-	-9,75,500.00
Ending balance(H=E+F+G)	5,10,000.00	12,40,500.00	21,50,000.00

Working Note 1
Calculation of collections per month
	January	February	March	April	May	June
Sales	6500000	7500000	6500000	9000000	10000000	11500000
Collection				6500000	8550000	8950000
Working		6500000*70/100	7500000*70/100	6500000*70/100+6500000*30/100	9000000*70/100+7500000*30/100	10000000*70/100+6500000*30/100
	Assumption: It is assumed that 30% collection is done 2 months after sales for eg. the collection of 30% sales for the month of January will be done in the month of April. It can also be assumed that the collection is done at the end of March and the figures will change accordingly.

Working Note 2
Calculation of accounts payable per month
	January	February	March	April	May	June	July
Sales	6500000	7500000	6500000	9000000	10000000	11500000	12500000
Cost of goods sold	1950000	2250000	1950000	2700000	3000000	3450000	3750000
Working	6500000*0.3	7500000*0.3	6500000*0.3	9000000*0.3	10000000*0.3	11500000*0.3	12500000*0.3
Amount of goods received	2160000	2040000	2475000	2910000	3315000	3660000
Working	2250000*70/100+1950000*30/100	1950000*70/100+2250000*30/100	2700000*70/100+1950000*30/100	3000000*70/100+2700000*30/100	3450000*70/100+3000000*30/100	3750000*70/100+3450000*30/100
Accounts payable				2380500	2649000	3063000
Working				2475000*70/100+2160000*30/100	2910000*70/100+2040000*30/100	3315000*70/100+2475000*30/100

Special Note: In case of queries please feel free to revert back to me. If you liked my answer and or presentation please upvote.Thank you.