Answered in Excel with equation answers as well

Answered In Excel with equation answers as well

AutoSave OFF OFF RESU Manufacturing Budgets.2 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Insert v Calibri (Body v 11 v Al A 2 Wran Text General 27 O LG Paste A Merge & Center S % Delete A Farmat Conditional Format Cell Formatting as Table Styles Sort & Filter Find Select Ideas Sensitivity A3 . x T B C D F H DG DH DJ DK DL DM 1 Name: Matt Your score: 0% 2 Matt Company, a manufacturer, predicts sales of 6,300 units in January, 5,850 units in February, 8,100 units in March, and 8,900 units in April. The company wants each month's ending finished goods inventory to equal 40% of next month's predicted unit sales. Beginning finished goods inventory for January is 2,520 units. Each unit of finished goods requires 0.40 pounds of direct materials at a cost of $10.00 per pound, and 5.0 hours of direct labor at a cost of $20 per hour. 3 Additionally, the company wants each month's ending raw materials inventory to equal 30% of next month's budgeted production. Prepare a sales budget, a production budget, a direct materials and a direct labor budget. 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Matt Company Budget Assumptions For the months of January, February, and March 2021 January February Selling price per unit $380 $380 Budgeted sales (units) 6,300 5,850 Budgeted sales (units) April Budgeted production (units) April Ratio of ending finished goods inventory to next month's sales Ratio of ending raw materials inventory to next month's requirements Number of units in finished goods inventory, January 1 Number of pounds in raw materials inventory, January 1 Number of pounds of raw materials required per unit Cost per pound of raw materials Number of direct labor hours required per unit Rate per direct labor hour March S380 8,100 8.900 9,300 40% 30% 2.520 734 0.40 $10.00 5.0 $20.00 25 25 Matt Company Sales Budget For the months of January, February, and March 2021 January February Budgeted sales (units) Selling price per unit Instructions Budgets + 27 28 29 30 March + 125% AutoSave OFF OFF RESU Manufacturing Budgets.2 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Insert v Calibri (Body v 11 v Al A 2 Wran Text General 27 O LE Paste CA Merge & Center SA Delete A Farmat Conditional Format Cell Formatting as Table Styles Sort & Filter Find Select Ideas Sensitivity A3 T B C D E F G H DG DH DK DL DM 25 26 27 28 29 30 Matt Company Sales Budget For the months of January, February, and March 2021 January February Budgeted sales (units) Selling price per unit Total budgeted sales (dollars) March March 31 32 33 34 35 36 37 38 39 40 41 42 43 Matt Company Production Budget For the months of January, February, and March 2021 January February Next month's budgeted sales (units) Ratio of inventory to future month's sales Budgeted ending inventory (units) Add: Budgeted sales (units) Required units of available production Deduct: Beginning Inventory (units) Budgeted units to produce 44 March 45 46 47 48 49 50 51 52 53 54 55 56 57 58 Matt Company Direct Materials Budget For the months of January, February, and March 2021 January February Budgeted units to produce Materials requirements per unit Materials needed for production (pounds) Add: Budgeted ending inventory (pounds) Total materials requirements (pounds) Deduct: Beginning inventory (pounds) Materials to be purchased (pounds) Material price per pound Total budgeted cost of direct materials purchases 59 Instructions Budgets + + 125% AutoSave OFF OFF RESU Manufacturing Budgets.2 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri Body v 11 Insert v v Al A Wraa Text General 27 O LG Paste CA Merge & Center SA Delete A Farmat Conditional Format Cell Formatting as Table Styles Sort & Filter Find Select Ideas Sensitivity A3 T A B C D E F G H DG DH DK DL DM Matt Company Direct Labor Budget For the months of January, February, and March 2021 January February Budgeted units to produce Direct labor requirements (hours) per unit Total direct labor hours needed March 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 Direct labor rate (per hour) Total budgeted direct labor cost 80 81 82 83 85 86 87 88 89 90 91 92 93 95 95 Instructions Budgets + + 125% AutoSave OFF OFF RESU Manufacturing Budgets.2 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Insert v Calibri (Body v 11 v Al A 2 Wran Text General 27 O LG Paste A Merge & Center S % Delete A Farmat Conditional Format Cell Formatting as Table Styles Sort & Filter Find Select Ideas Sensitivity A3 . x T B C D F H DG DH DJ DK DL DM 1 Name: Matt Your score: 0% 2 Matt Company, a manufacturer, predicts sales of 6,300 units in January, 5,850 units in February, 8,100 units in March, and 8,900 units in April. The company wants each month's ending finished goods inventory to equal 40% of next month's predicted unit sales. Beginning finished goods inventory for January is 2,520 units. Each unit of finished goods requires 0.40 pounds of direct materials at a cost of $10.00 per pound, and 5.0 hours of direct labor at a cost of $20 per hour. 3 Additionally, the company wants each month's ending raw materials inventory to equal 30% of next month's budgeted production. Prepare a sales budget, a production budget, a direct materials and a direct labor budget. 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Matt Company Budget Assumptions For the months of January, February, and March 2021 January February Selling price per unit $380 $380 Budgeted sales (units) 6,300 5,850 Budgeted sales (units) April Budgeted production (units) April Ratio of ending finished goods inventory to next month's sales Ratio of ending raw materials inventory to next month's requirements Number of units in finished goods inventory, January 1 Number of pounds in raw materials inventory, January 1 Number of pounds of raw materials required per unit Cost per pound of raw materials Number of direct labor hours required per unit Rate per direct labor hour March S380 8,100 8.900 9,300 40% 30% 2.520 734 0.40 $10.00 5.0 $20.00 25 25 Matt Company Sales Budget For the months of January, February, and March 2021 January February Budgeted sales (units) Selling price per unit Instructions Budgets + 27 28 29 30 March + 125% AutoSave OFF OFF RESU Manufacturing Budgets.2 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Insert v Calibri (Body v 11 v Al A 2 Wran Text General 27 O LE Paste CA Merge & Center SA Delete A Farmat Conditional Format Cell Formatting as Table Styles Sort & Filter Find Select Ideas Sensitivity A3 T B C D E F G H DG DH DK DL DM 25 26 27 28 29 30 Matt Company Sales Budget For the months of January, February, and March 2021 January February Budgeted sales (units) Selling price per unit Total budgeted sales (dollars) March March 31 32 33 34 35 36 37 38 39 40 41 42 43 Matt Company Production Budget For the months of January, February, and March 2021 January February Next month's budgeted sales (units) Ratio of inventory to future month's sales Budgeted ending inventory (units) Add: Budgeted sales (units) Required units of available production Deduct: Beginning Inventory (units) Budgeted units to produce 44 March 45 46 47 48 49 50 51 52 53 54 55 56 57 58 Matt Company Direct Materials Budget For the months of January, February, and March 2021 January February Budgeted units to produce Materials requirements per unit Materials needed for production (pounds) Add: Budgeted ending inventory (pounds) Total materials requirements (pounds) Deduct: Beginning inventory (pounds) Materials to be purchased (pounds) Material price per pound Total budgeted cost of direct materials purchases 59 Instructions Budgets + + 125% AutoSave OFF OFF RESU Manufacturing Budgets.2 Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri Body v 11 Insert v v Al A Wraa Text General 27 O LG Paste CA Merge & Center SA Delete A Farmat Conditional Format Cell Formatting as Table Styles Sort & Filter Find Select Ideas Sensitivity A3 T A B C D E F G H DG DH DK DL DM Matt Company Direct Labor Budget For the months of January, February, and March 2021 January February Budgeted units to produce Direct labor requirements (hours) per unit Total direct labor hours needed March 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 Direct labor rate (per hour) Total budgeted direct labor cost 80 81 82 83 85 86 87 88 89 90 91 92 93 95 95 Instructions Budgets + + 125%