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 28x5 Forecasted Sales (in March April May 8, 5ee 7,5e0 10,000 12,580 13,500 13,58a 14,500 14,580 13,500 15,580 July August 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 . The purchase of el eferences month after the sale, and the remaining 40 percent two months after 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 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 expenses are projected to be $15.720,000 for the year. The breakdown of these expenses is presented e. 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 $3,100 3,600 ,368 2,500 ueilities Total

Answer 1

Answer:

E12 | | cash budget january february S 7500 $ 8500 april may junel august september october november december 7,500 $13,500 $13,500 $ 14,500 $ 14,500 13,500 S 15,500 2,625 | S 3,500| S 3,850|S4,375|$ 4,725 $ 4,725 $ 5,075 $ 5,075 $ 4,725 $ 5,425 $10,000 $11,000 $12.500 500 13,500 $1 S 2,625 2,083 2.975 S 1,838 S $ 4,375 4,725 4,725 5,075 5,05 4,725 5,425 cost of goods sold purchases (1 month prior) purchases (month of sale 2,450 S2.695 S 3,063 3,303 3,308 3,553 3,553 S 3,308 S 3,798 $ 788 $ 893 788 $1.050 1.155 1.313 1,418 1,418 1,523 1523 1418 1,628 3,238 IS 3,745|$ 4,218|S 4,620| S 4,725 $4,970 $ 5,075 $ 4,830 $ 5,215 $ 1,628 total receipts in each month 2,870 2,730 S 2,009S payment for accounts payable (1month after) paytfent for accounts payable (2 month aftet) total payment for accountspaabe 1,911 2,266 $ 2,622 2.952 3,234 3,308 3,479 S 3,553 3,381 S 3,651 861 IS 819|$ 971l s 1.124 IS 1,265 $ 1.386 $ 1,418 $ 1,491 $ 1,523 $ 1,449 2,772 $ 3,085 3.593 $ 4,076 4499 4,694 4897 $ 5,044 4,904 5,100 cash budget for the second quarter une s 520 S 520 S 1,402 beginning balance collections february sales march sales april sales may sales 3,400 4,500 S 3,000 6,000 ,000 S 7.900 S 9,000 $10,600 total cash available s 3,085 S 3,593 4,076 3,000 3,300 S 3,750 900 accounts payable wages general and administrative property taxes income taxes total disbursements cash balance cash borrowed 900 900 S 195 S 1,727 2,936 S 325 ending balance

for formulas and calculations, refer to the image below E12 ▼ ● | x v 丘| | cash budget september october november dece 14500 13500 15500 january february 7500 00 00 "CS"0.55 "ОЗ"0.35 "ES"O.35 .04-0.7 ·E4-0.7 -F4-0.7 -c4"О.3 tO4"Q3 64°0.3 10000 11000 13500 3500 14900 13 0.35 5 0.35 K-0.35 L3 0.35 M 0.55 N5 0.35 sales cost of goods soid purchases (1 month prior, purchases (month or sale) total receipts in each month 5.0.35 14 0.7 ,H4-0.3 0.35 -G4.0.7 -F4 0.3 0.7 K4 0.7 4 0.7 -G4 0.3 -C5 C6 -05 D6 -ES E6 C7-0.7-07 0. 0.7 .07.0.3 «G7.0.7 F7-0.3 payment lor acuspabie 2 month aftet) E7-0.3 13 14 15 for the second querter beginning balance F33 -ОЗ"0.4 -B-0.6 march sales opril sales 19 3 0.6 G3-0.6 SUMIH17H H15+ 21 SUMIG17 G15 G21 F15 F22 23 G10 2G3-О 3 accounts payable F10 03 -0.3 15720-1360-3560/12 25 eneral and administrative (15720-1360-3560)/12(15720-1360-3560)/12 property taxes total disbursements SUMIF24F F22-629 -F33-F30 32 balance G30-G32 H30