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		$	8,000
February			9,000
March			8,000
April			10,500
May			11,500
June			13,000
July			14,000
August			14,000
September			15,000
October			15,000
November			14,000
December			16,000

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 35 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,770,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,200
  Promotion		3,700
  Property taxes		1,390
  Insurance		2,170
  Utilities		1,700
  Depreciation		3,610
  Total	$	15,770

• 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,200,000. The company pays 100 percent of the estimated tax payment.
• Alpha-Tech maintains a minimum cash balance of $530,000. If the cash balance is less than $530,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 $530,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.)

	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

Answer 1

Purchase working
	February	March	April	May	June	July
Sales	9000000	8000000	10500000	11500000	13000000	14000000
COGS (35% of sales)	3150000	2800000	3675000	4025000	4550000	4900000
Purchase (30% of same month COGS + 70% of next month COGS)	2905000	3412500	3920000	4392500	4795000
Purchase payment			3260250	3767750	4250750

Sales receipts are calculated 60% of prior month sales and 40% of two months prior sales.

General and administrative expenses
	April	May	June
All General and administrative expenses excluding Property taxes	1198.333333	1198.333333	1198.333333
Property taxes			347.5
Total General and administrative expenses	1198.333333	1198.333333	1545.833333

Income tax is calculated on $ 3200000 and not derived from other figures in the question. Both don't match.

ALPHA-TECH
Cash Budget
For the Second Quarter of 20x5
	April	May	June
Beginning balance	530,000	530,000	1,125,334
Collections:
February sales	3,600,000
March sales	4,800,000	3,200,000
April sales		6,300,000	4,200,000
May sales			6,900,000
Total receipts	8,400,000	9,500,000	11,100,000
Total cash available	8,930,000	10,030,000	12,225,334
Disbursements:
Accounts payable	(3,260,250)	(3,767,750)	(4,250,750)
Wages	(3,150,000)	(3,450,000)	(3,900,000)
General and administrative	(1,198,333)	(1,198,333)	(1,198,333)
Property taxes			(347,500)
Income taxes	(1,280,000)
Total disbursements	(8,888,583)	(8,416,083)	(9,696,583)
Cash balance	41,417	1,613,917	2,528,751
Cash borrowed	488,583
Cash repaid	-	(488,583)
Ending balance	530,000	1,125,334	2,528,751