Question

Requirements Prepare the sales budget, including a separate section that details the type of sales made (cash versus credit) for the first three months of the year, as well as a summary budget for the quarter. 2 Prepare the production budget for the first three months of the year, as well as a summary budget for the quarter. a. Enter all amounts as positive values. Do NOT use parentheses or a minus sign for amounts to be subtracted Prepare the direct materials purchases budget for the first three months of the year, as well as a summary budget for the quarter. Assume the company needs 120,000 board feet of wood for production in April. a. Enter all amounts as positive values. Do NOT use parentheses or a minus sign for amounts to be b. Abbreviation used: DM Direct materials subtracted 4 Prepare the cash collections budget for January, February, and March, as well as a summary for the first quarter. 5 Prepare the cash payments budget for direct materials purchases for the months of January, February, and March, as well as a summary for the first quarter. Units October actual sales (prior year) November actual sales (prior year) December actual sales (prior year) January projected sales February projected sales March projected sales April proiected sales 92,000 85,000 78,000 80,000 90,000 95,000 105,000 20% of the total sales are cash sales 80% of the total sales are credit sales The company's collection history indicates that: 80% of credit sales is collected in the month after the sale 10% is collected two months after the sale 6% is collected three months after the sale 4% is never collected 1. Prepare the sales budget, including a separate section that details the type of sales made (cash versus credit) for the first three months of the year, as well as a summary budget for the quarter. (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instruction tab you will be marked wrong.) Birdfeeders Unlimited Sales Budget For the Quarter Ended March 31 Month Januar Februar March 1st Quarter Unit sales (birdfeeders Multiply by: Selling price per birdfeeder Total sales revenue Type of sale Cash sales Credit sales Total sales revenue 2. Prepare the production budget for the first three months of the year, as well as a summary budget for the quarter. (Always use cell references and formulas where appropriate to receive full credit. Use cell references from requirement 1 and prior calculations, if applicable. If you copy/paste from the Instruction tab you will be marked wrong.) Birdfeeders Unlimited Production Budget For the Quarter Ended March 31 Month Januar Februar March 1st Quarter Unit sales (birdfeeders Plus: Desired ending inventory Total needed Less: Beginning inventory Number of units to produce 3. Prepare the direct materials purchases budget for the first three months of the year, as well as a summary budget for the quarter. Assume the company needs 120,000 board feet of wood for production in April. (Always use cell references and formulas where appropriate to receive full credit. Use cell references from prior requirements and calculations, if applicable. If you copy/paste from the Instruction tab you will be marked wrong.) Birdfeeders Unlimited Direct Materials Budget For the Quarter Ended March 31 Month Januar Februa March 1st Quarter Units (birdfeeders) to be produced Multiply by: Quantity (board feet) of DM needed per unit Quantity (board feet) needed for production Plus: Desired ending inventory of DM Total quantity (board feet) needed Less: Beginning inventory of DM Quantity (board feet) to purchase Multiply by: Cost per board foot Total cost of DM purchases 4. Prepare the cash collections budget for January, February, and March, as well as a summary for the first quarter. (Always use cell references and formulas where appropriate to receive full credit. Use cell references from previous calculations and requirements, if applicable. If you copy/paste from the Instruction tab you will be marked wrong.) Birdfeeders Unlimited Cash Collections Budget For the Quarter Ended March 31 Month Januar Februa March 1st Quarter Cash sales in current month Collection on credit sales: Credit sales made one month ago Credit sales made two months ago Credit sales made three months ago Total cash collections 5. Prepare the cash payments budget for direct materials purchases for the months of January, February, and March, as well as a summary for the first quarter. (Always use cell references and formulas where appropriate to receive full credit. Use cell references from previous calculations and requirements, if applicable. If you copy/paste from the Instruction tab you will be marked wrong.) Birdfeeders Unlim ted Cash Payments for Direct Materials Purchases For the Quarter Ended March 31 Month January- -FebruaryーーMarch 1stQuarter Total cost of DM purchases Cash payments for current month purchases Cash payments for prior month purchases Total cash payments Birdfeeders Unlimited makes backyard birdfeeders. The company sells the birdfeeders to home improvement stores $15 Sales price per birdfeeder 1.5 Board feet of wood required for each birdfeeder $4 Cost per board foot (actual) 10% Desired ending wood inventory stated as a percentage of next month's production requirements 20% Desired ending finished goods inventory (finished birdfeeders) stated as a percentage of next month's sales $550,000 Total cost of direct materials purchases in December 45% of direct materials purchases are paid in the month of purchase 55% of direct materials purchases are paid in the month after purchase

Answer 1

В 12 A B units october sales november sales december sales anuay sales february sales march sales april sales 92,000 85,000 78,000 80,000 90,000 95,000 105,000 sales price per birdfeeder 15.00 1.50 board feet of woof required for each unit 4.00 cost per board foot 6 10 1 Sales budget februa march 12 13 14 15 16 17 18 19 20 21 anua 1st quarter budgeted sales in units sellin total sales 90,000 95,000 265,000 15.00 3,975,000 80,000 15.00 15.00 $ 15.00 $ ce per unit $1,200,000.00 $1,350,000.00 $1,425,000.00 of sale 240,000.00 270,000.00 285,000.00 $ 960,000.00 $1,080,000.00 $1,140,000.00 1,200,000.00 $1,350,000.00 $1,425,000.00 cash credit total sale revenue 795,000 3,180,000 3,975,000 februa uction budget march anua 1st quarter sales units add: desired ending invento total needs less: beginning invento 23 80,000 18,000 98,000 16,000 82,000 90,000 19,000 109,000 18,000 91,000 95,000 21,000 116,000 19,000 97,000 265,000 58,000 323,000 53,000 270,000 25 26 27 uction units

februa 29 3 material purchases budget uction units march 1st quarter anua 270,000 1.50 405,000 40,200 445,200 38,200 407,000 4.00 S506,600 549,600 571,800 1,628,000 82,000 1.50 123,000 13,650 136,650 10,000 126,650 91,000 1.50 136,500 14,550 151,050 13,650 137,400 97,000 1.50 145,500 12,000 157,500 14,550 142,950 direct material required per unit (board feet) total direct material requirement add: desired ending invento total needs less: beginning invento direct material purchases (board feet direct material cost per (board feet total direct material purchases ($ 31 32 34 4.00 $ 4.00 $ 4.00 $ 37 39 4 Schedule of expected cash collections februa march 1st quarter anua cash sales in current month collection on credit sales credit sales made one month ago credit sales made two months ago credit sales made three months ago total cash collections 270,000 $ 285,000 795,000 41 42 43 864,000 2,568,000 340,500 70,200 229,500 $ 1,386,300 $1,231,500 1,315,200 3,933,000 $ 936,000 $ 936,000 $ 768,000 $ $127,500$1 $82,800$76,500 117,000 $96,000 45 47 februa 49 5 cash payments budget 1st quarter $506,600 549,600571,8001,628,000 $227,970$247,320257,310 732,600 $302,500278,630 302,280$ 883,410 $530,470 $525,950$559,5901,616,010 march anua total direct material purchases ($) for current month purchases for prior month purchases total cash payments 51 52 53 54

for formulas and calculations, refer to the next image -

812 units sales price per birdfeeder board feet of woof required for each unit cost per board foot october sales november sales december sales 92000 15 1.5 anuary saleS february sales march sales april sales 105000 10 februa 1 Sales budget march 1st quarter anua 13 14 15 16 17 18 19 20 21 budgeted sales in units selling price per unit total sales ype of sale cash credit total sale revenue -D7 $F$3 -E13 E14 SUM(D13:F13 SF$3 F13 F14 SF$3 D13 D14 SUM(D15:F15 SUM(D17:F17 -SUM(D18:F18 SUM(D19:F19 -015*20% :015*80% D17+D18 -E15°2096 -F15*20% F15*80% -F17+F18 15 *8096 E17+E18 uction budget februa march 1st quarter -SUM(D23:F23 SUM(D24:F24 -SUM(D25:F25 SUM (D26:F26 SUM(D27:F27 anua E13 -F23°2096 E23+E24 -D24 -E25-E26 sales units add: desired ending invento total needs less: beginning invento D13 E23*2096 -D23+D24 :023 *2096 D25-D26 F13 -09"20% -F23+F24 -E24 -F25-F26 23 24 25 26 uction units

829 3 material purchases budget february march 1st quarter SUM(D30:F30 -SFSA SUM(D32:F32 SUM(D33:F33) -SUM(D34:F34 SUM(D35:F35 -SUM(D36:F36 $F$5 -SUM(D38:F38 anua 30 -E27 uction units D27 F27 direct material required per unit (board feet) SF$A total direct material requirement add: desired ending invento total needs less: beginning invento direct material purchases (board feet direct material cost per (board feet total direct material purchases ($) 32 D30 D31 :E32*1096 D32+D33 10000 -D34-D35 $F$5 -D36 D37 E30 E31 -F32*1096 E32+E33 D33 E34-E35 $F$5 E36 E37 F30 F31 120000*1096 -F32+F33 -E33 F34-F35 34 36 37 38 39 40 F36 F37 februa 4 Schedule of expected cash collections march 1st quarter anua -F17 cash sales in current month collection on credit sales credit sales made one month ago credit sales made two months ago credit sales made three months ago total cash collections -D17 SUM(D41:F41 E17 78000"SFS3*8096 -85000"SFS3*1096-78000*SFS3*10% -92000*SFS3*636 |-85000°SF$3*6% -D41+D43+D44+D45 E41+E43+E44+E45 |D18*8096 E18"80% |:018*1096 |-78000*SFS3"6% F41+F43+F44+F45 -SUM(D43:F43 -SUM(D44:F44 |rSUM(D45:F45 SUM D46:F46 cash payments budget februa march 1st quarter G38 SUM(D51:F51 SUM(D52:F52 -SUM(D53:F53) anua total direct material purchases ($ for current month purchases for prior month purchases total cash payments -E38 -E50*45% D50*55% -E51+E52 -D38 :050*45% :550000* 55% -D51+D52 50 -F38 -50*45% E50*55% -F51+F52 53