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 (in thousands) 8,see 18,000 11,000 12,500 13, see 13,580 Apri1 14,500 13, 5e0 15,5ee 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. eferences is expected to continue, Sixty percent of billings are collected the . The purchase of electronic components is Alpha-Tech's largest expenditure, and each month's cost of goods sold is estimated to . Historically, 70 percent of accounts payable has been paid one month after receipt of the purchased components, and the . Hourly wages and fringe benefits, estimated to be 30 percent of the current month's sales, are paid in the month incurred. month after the sale, and the remaining 40 percent two months after be 35 percent of sales. Seventy percent of the parts are received by Alpha-Tech one month prior to sale and 30 percent are remaining 30 percent has been paid two months after receipt in the following schedule. All cash expenditures are paid uniformly throughout the year except the property taxes. which are paid in of sale . General and administrative e are projected to be $15720,000 for the year. The breakdown of these expenses is preserd four equal installments at the end of each quarter 3,600 1,360 2,500 1,600 Property taxes Insurance utilities 15,728

Answer 1

for formulas and calculations, refer to the image below -

E12 X cash budget 0 sales cost of goods sold january february 7500 8500 7500 -C3 0.35 -D3*0.35E3"0.35 july august september october november december 13500 13500 14500 -13 0.35 13 0.35K3 0.35 L3 0.35 M3 0.35 N3 0.35 10000 -F3 0.35 -G4 0.7 -F4 0.3 FS+F6 -E7.0.7 1000 -G3 0.35 -H4 0.7 -G4 0.3 -GS+G6 F7.0.7 -E7 0.3 4500 13500 5500 purchases (1 month prior)D4-0.7 -E40.7 -F4 0.7 purchases (month of saleC4 0.3 -04 0.3 -E4-0.3 total receipts in each month C5+06 D5+D6 ES+E6 -H3 0.35 -14 0.7 -H4 0.3 payment for aocounts payable(1 month after pagment for acccunts pagable (2 month alter) otal payment or acooutspayable -G7 0.7 -F7 0.3 «H7.0.7 :17.0.7-17.0.7 L0 F8+F9 12 13 14 15 16 17 18 19 cash budget for the second quarter beginning balance 520 -F33 -G33 february sales march sales april sales D3 0.4 -E3.0.6 -E3 0.4 -F3 0.6 3 0.4 -G3 0.6 rSUM(H17:H20) -H15 H21 21 total receipts total cash avallable -SUMIF17 F20 -SUM(G17:G20 -G15+G21 F15+F21 accounts payable WaECS general and administrative15720-1360-3560)/12-(15720-1360-3560)/12 property taxes -F10 F3 0.3 -G10 -G3 0.3 30.3 -15720-1360-3560)/12 3100 0.4 total disbursements cash balance cash borrowed cash repaid ending balance -SUMI F24:F28 SUM G24 G28 -G22-G29 SUMIH24:H28) -H22-H29 30 F22-F29 -F33-F30 32 -F31 -G30-G32 -H30 34