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,000
February			11,000
March			10,000
April			12,500
May			13,500
June			15,000
July			16,000
August			16,000
September			17,000
October			17,000
November			16,000
December			18,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. 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 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, 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,970,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,600
  Promotion		3,900
  Property taxes		1,480
  Insurance		1,580
  Utilities		1,600
  Depreciation		3,810
  Total	$	15,970

• 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,600,000. The company pays 100 percent of the estimated tax payment.
• Alpha-Tech maintains a minimum cash balance of $570,000. If the cash balance is less than $570,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 $570,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.)
   Cash Budget For the Second Quarter of 20x5 May April June Beginning balance Collections: February sales March sales April sales May sales Total receipts $ 0 $ $ Total cash available 0 Disbursements: Accounts payable Wages General and administrative Property taxes Income taxes $ Total disbursements 0 Cash balance $ 0 Cash borrowed Cash repaid Ending balance
   

Answer 1

Cash budget for Alpha-Tech for the 2nd Quarter (ie. April,May and June ) : -
Particulars		February	March	April	May	June	July
Sales		11000	10000	12500	13500	15000	16000
Collection : Cash Inflow
70% of bill collected in 1 month				7000	8750	9450	10500
30% of bill in 2 months				3300	3000	3750	4050
Total inflow				10300	11750	13200	14550
Purchase of electronic component		4400	4000	5000	5400	6000	6400
70% of purchase of electronic component is received 1 month advance		2800	3500	3780	4200	4480
30% of purchase if received in month of sale itself		1320	1200	1500	1620	1800	1920
Amount paid : Cash outflow
70% in 1 month after becoming payable		0	2884	3290	3696	4074	4396
30% in 2months after amount payable		0	0	1236	1410	1584	1746
Total paid for purchase		0	0	4526	5106	5658	6142
Disbursements :
Hourly wages and fringe benfits		3300	3000	3750	4050	4500	4800
salary and fringe benefits		300	300	300	300	300	300
Promotion		325	325	325	325	325	325
Insurance		131.6666667	131.6666667	131.6666667	131.6666667	131.6666667	131.6666667
Utilities		133.3333333	133.3333333	133.3333333	133.3333333	133.3333333	133.3333333
Depreciation		317.5	317.5	317.5	317.5	317.5	317.5
Propert taxes ( Paid quarterly at end of each quarter )		0	370	0	0	370	0
Income tax paid		0	0	1440000	0	0	0
Total cash outflow (Depreciation not taken )				1449166	10046	11418
Opening cash balance on 1 april 2015				570000	570000	570000
Add : cash Inflow				10300	11750	13200
Less: Cash outflow				-1449166	-10046	-11418
Net Cash ending balance				-868866	571704	571782
Minimum cash balance required				570000	570000	570000
Borrowings taken/paid				1438866	-1704	-1782