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.)

Answer 1

	Cash Budget
	Second Quarter (Amounts in thousands)
	Particulars	April	May	Jun
	Beginning cash balance	$          510.00	$          510.00	$       1,146.00
	Receipts:
	Collections of sales                                                ( Refer working note 5 )	$       6,800.00	$       8,250.00	$       9,700.00
	Total cash available	$       6,800.00	$       8,250.00	$       9,700.00
	Disbursements:
	Payment of inventory purchases                           ( Refer Working Note 4)	$      (2,344.50)	$      (2,779.50)	$      (3,193.50)
	Labour Cost (Working Note 1)	$      (2,700.00)	$      (3,000.00)	$      (3,450.00)
	General & Administrative Expenses (except property tax expenses) ( Working Note 2)	$      (1,195.00)	$      (1,195.00)	$      (1,195.00)
	Property Tax expenses ( Working Note 3)	$                  -	$                  -	$         (332.50)
	Income Tax payment ( $ 3000 * 40% * 100%)	$      (1,200.00)	$                  -	$                  -
	Total disbursements	$      (7,439.50)	$      (6,974.50)	$      (8,171.00)
	Cash balance*	$         (129.50)	$       1,785.50	$       2,675.00
	Bank loan**	$          639.50	$                  -	$                  -
	Repayment of bank loan	$                  -	$         (639.50)	$                  -
	Adjusted cash balance	$          510.00	$       1,146.00	$       2,675.00
	*Cash Balance= Beginning cash bal + recipt + payment
	** Since the Cash balance is -129500 in april which is less than $ 510000, so bank automatically extends of credit so that the checking account balance is at least $510000 at month-end.Accordingly $ 639500 has been extended as credit in april and repaid in may when there was excess money.
Working Note 1	Particulars	Amount paid in (in Thousands)
		April	May	Jun
	Credit Sales	$             9,000.00	$          10,000.00	$          11,500.00
	30% of Each month sales paid in the same month	$             2,700.00	$             3,000.00	$             3,450.00
Working Note 2	General & Administrative Expenses (except property tax expenses) per month	=	Total General & Administrative Expenses - Property tax Expenses
			12 months
		=	15,670 - 1,330
			12
		=	$             1,195.00
Working Note 3	Property tax expenses ( paid in jun)	=	Total Property tax expenses
			4 qtr
		=	1,330
			4
		=	$                332.50
Working Note 4	Calculation of Cash paid to creditors
	Note:- Raw Material shall be the 30% of sales of each month out of which 70% Material received in last month and remaning 30% material received in the month of sale which shall be paid as 70% of the purchase of last month and 30 % of the purchase of last to last month.

Particulars	Amount paid in ( in thousands)
	Jan	Feb	Mar	April	May	Jun	July
Credit Sales	$             6,500.00	$             7,500.00	$             6,500.00	$             9,000.00	$          10,000.00	$          11,500.00	$          12,500.00
Cost of Sales (30% of Sales)	$             1,950.00	$             2,250.00	$             1,950.00	$             2,700.00	$             3,000.00	$             3,450.00	$             3,750.00
Purchase of Material (70% of next month sales)	$             1,575.00	$             1,365.00	$             1,890.00	$             2,100.00	$             2,415.00	$             2,625.00	$                          -
Purchase of Material (30% of current month sales)	$                585.00	$                675.00	$                585.00	$                810.00	$                900.00	$             1,035.00	$             1,125.00
Total Creditors (Total Purchase)	$             2,160.00	$             2,040.00	$             2,475.00	$             2,910.00	$             3,315.00	$             3,660.00	$             1,125.00
Amount paid of:-
70% of Last month		$             1,512.00	$             1,428.00	$             1,732.50	$             2,037.00	$             2,320.50	$             2,562.00
30 % of Last to last month			$                648.00	$                612.00	$                742.50	$                873.00	$                994.50
Total Cash paid to creditors				$             2,344.50	$             2,779.50	$             3,193.50

Working Note 5	Statement showing calculation of Cash Collection Schedule for the second quarter
	Note: Debtors collection would be 70% of last month sales,30% of last to last month sales.

Particulars	Amount received in (in thousands)
	Jan	Feb	Mar	April	May	Jun
Credit Sales	$             6,500.00	$             7,500.00	$             6,500.00	$             9,000.00	$          10,000.00	$          11,500.00
Amount received of:-
70% of Last month		$             4,550.00	$             5,250.00	$             4,550.00	$             6,300.00	$             7,000.00
30 % of Last to last month			$             1,950.00	$             2,250.00	$             1,950.00	$             2,700.00
Total Cash Collection				$             6,800.00	$             8,250.00	$             9,700.00