Manufacturing Setup Calculations Direct Materials Usage Budget for Year Ended 30th June 2022 Total Setup Hours Casual Table 1,200 12,000 0.20 Number of setups required for year) Total setup hours expected Total Setup labour hours (per indivdual unit produced) Deluxe Table 250 3,000 0.30 Materials Granite 15,000 Red Oak Physical Units Budget: Direct Materials required for Casual Tables Direct Materials required for Deluxe Tables Total quantity of DM to be used in production Manufacturing Overhead Costs Budget for Year Ended 30th June 2022 720,000 b.f 120,000 b.f 840,000 b.f 360,000 sq.ft. 80,000 sq.ft. 440,000 sq.ft. $ $ $ Manufacturing Operations Overhead Costs Variable costs (based on DML hours): Supplies Indirect manufacturing labour Power Maintenance 1,500,000 1,680,000 2,100,000 1,200,000 490,000 Cost Budget: Available from beginning direct materials inventory (under FIFO cost-flow assumption, as given) Red Oak Granite To be purchased and used this period: Red Oak Granite Total costs of DM to be used this period 600,000 5,390,000 3,800,000 $4,400,000 Fixed costs: Depreciation Supervision $10,280,000 Power Maintenance 1,020,000 390,000 630,000 480,000 $5,880,000 Total manufacturing operations overhead costs 9,000,000 Direct Materials Purchases Budget for Year Ended 30th June 2022 $ Materials Granite Machine Setup Overhead Costs Variable costs (based on setup hours): Supplies Total Indirect manufacturing labour Power Red Oak 390,000 840,000 90,000 Physical Units Budget: To be used in production (from DM Usage budget) Add target ending inventory Total requirements Deduct beginning inventory on hand Purchases of DM to be made (units) 840,000 b.f. 80,000 b.f. 920,000 b.f. 70,000 b.f. 850,000 b.f. 440,000 sq.ft 20,000 sq.ft 460,000 sq.ft 60,000 sq.ft 400,000 sq.ft Fixed costs: Depreciation Supervision Power 603,000 1,050,000 27,000 Total manufacturing setup overhead costs 3,000,000 $5,950,000 Cost Budget: Red Oak @ $7 per b.f Granite @ $10 per sq. ft. Total cost of DM to be purchased this year $4,000,000 $4,000,000 Total manufacturing overhead costs 12,000,000 $5,950,000 $9.950,000 The following data is available for the 2022 budget: Non Manufacturing Costs Direct Materials Red Oak Granite $1,024,000 6.50% $1,330,000 Direct manufacturing labour $7 per board foot (b.f.) (same as in 2021) Product design - fixed cost only $10 per square foot (sq.f.) (same as in 2021) Marketing costs: Variable (for all products: as a percentage of revenue) $20 per hour Fixed Distribution costs: Content of Each Finished Product Unit Variable (per cubic foot for all products) Casual Table De luxe Table Fixed 12 12 6 8 Shipping details: 4 6 Package shipping size (per product unit) (cubic feet) $2 $1,596,000 Red Oak (board feet) Granite (square feet) Direct manufacturing labour (hrs) Casual Table 18 Deluxe Table 24 Finished Product Inventories and Sales Revenues Budget for the Year Ended 30th June 2022 Casual Table Deluxe Table Units Expected sales in units Selling price Target ending inventory (in units) Beginning inventory (in units) Beginning inventory (in dollars) 50,000 S600 11,000 1,000 $384.000 10,000 Product $800 500 Casual Table 500 Deluxe Table $262.000 Total Selling Price $ 600 800 50,000 10,000 Total Revenues $ 30,000,000 8,000,000 38.000.000 Red Oak b.ft. Granite sq.f. Production (units) Budget for Year Ended 30th June 2022 Beginning inventory 70,000 60,000 Target ending inventory 80,000 20,000 Product Casual Deluxe Manufacturing Overhead Costs & Activities Allocations Budgeted sales (in units) 50,000 10,000 Plus targeted ending inventory 11,000 500 Mfg Operations Mfg Setup Activity Costs Pool Costs Pool Total required units: 61,000 10,500 Manufacturing - Variable costs (cost per unit of cost driver) Supplies $5 $26 Less opening inventory (units) on hand 1,000 500 Indirect Mfg labour S6 556 Units of finished goods to be produced 60.000 10.000 S7 S6 Maintenance $4 so Direct Manufacturing Labour Costs Budget for Year Ended 30th June 2022 Power DML Hours per Unit Total DML Hours Cost driver for variable (and fixed) costs: Mfg labour hours Hourly Wage Rate Setup labour hours Product Units produced 60,000 4 6 Total Cost $ 4.800,000 1,200,000 6.000.000 Manufacturing - Fixed costs: Depreciation Supervision Power Maintenance $20 $20 10,000 240,000 60.000 300,000 Casual Tables $603,000 Deluxe Tables $1,050,000 Total DML hours and cost $27,000 SO $1,020,000 $390,000 $630,000 $480,000 Manufacturing Setup Activity Number of product units produced per batch Setup time (hrs) per batch Casual Table 50 10 Deluxe Table 40 12 Cost of Goods Sold Budget for Year Ending 30th June 2022 Unit Cost Calculations for Ending Inventory of Finished Goods Sourcing Budget Total $ $ Provided Info 646,000 Unit of Input 7 10 20 30.0 200.0 Input Component Red oak Granite Direct manufacturing labour Manufacturing operations overhead Machine setup overhead Total Unit Cost per item Product Casual Tables Deluxe Tables Opening Finished goods as at 1st July 2021 Input Total Volume of Total Units Value $ Input Units Value $ Add Cost of goods manufactured: 12 b.f. 84 12 b.f. 84 Direct materials used 6 sq.ft 60 8 sq.ft. 80 Direct manufacturing labour 4 hrs 80 6 hrs 120 Manufacturing overhead incurred 4 hrs 120 6 hrs 180 Total cost of goods manufactured 0.20 hrs 40 0.30 hrs 60 Cost of goods available for sale 384 524 Deduct Closing finished goods as at 30th June 2022 Cost of goods sold DM DML 10,280,000 6,000,000 12,000,000 Mfg OH 28,280,000 28,926,000 4,486,000 24,440,000 Inv Nonmanufacturing Cost Budget for Year Ending 30th June 2022 Ending Inventories Valuation Budget as at 30th June 2022 Business Function Variable costs $ Fixed Costs $ Total $ Quantity on hand Cost per Unit Inventory Item $ Total Product design Marketing costs Distribution costs Total nonmanufacturing costs 2,470,000 2,280,000 1,024.000 1,330,000 1,596,000 Direct Materials 1,024,000 3,800,000 3,876,000 8,700,000 Red oak # 80,000 20,000 $7 $10 560,000 200,000 Granite # 760,000 Budgeted Operating Income Statement for Year Ending 30th June 2022 WIP (Negligible amounts, as advised) Nil Supporting Budget $ $ Finished Goods Casual tables Deluxe tables # 11,000 500 $384 $524 4.224.000 262,000 Rev COGS # 38,000,000 24.440.000 13,560,000 4,486,000 Revenue Less Cost of goods sold Gross margin Less Operating Expenses Product design Marketing Distribution Total value of Ending Inventories 5.246,000 Non Mfg Non Mfg Non Mfg 1,024,000 3,800,000 3,876,000 [# Note that under the FIFO cost flow assumption we are valuing closing inventories on hand at only the current year unit costs, as usage/sales of the inventory in the current budgeted year is greater than the opening inventory levels. 8,700,000 4.860.000 Operating Income (pre tax) not add rows or colums or change the structure/layout of this worksheet. sensitivity analysis change the GREEN cells Paste values from the Live Case into these five scenarios Base case No chairs No sales uplifts No closing inventory Chairs to meet Strat. Plan Profit Live case These are input cells that drive through the whole of the budget model 115,000 S110 0 SO 115,000 $110 115,000 $110 $110 INPUTS Chairs: Full year units sold Chairs: Sell price Casual Tables sales uplift Deluxe Tables sales uplift Ending inventory (units): Casual Tables Deluxe Tables Chairs 4.0% 3.0% 0.0% 0.0% 0.0% 0.0% 4.0% 3.0% 4.0% 3.0% 11,000 500 10,000 11,000 500 0 11,000 500 10,000 OOO 11,000 500 10,000 56.17 22.00 6.50 4.77 These are output cells that need to come through from the budget model paste this value into the table paste this value into the table paste this value into the table paste this value into the table paste this value into the table OUTPUTS SM Revenue Gross Profit Net Income Cash at Bank - year end Bank Overdraft - year end DIFFERENCES TO BA SE CASE SM Revenue Gross Profit Net Income Cash at Bank - year end Bank Overdraft - year end PATCH Cells E29 to E33 should be formulas (not values). To patch: copy cells G29:G33 and paste into cells E29:E33 -56.17 -22.00 -6.50 -4.77 0.00 -56.17 -22.00 -6.50 -4.77 0.00 -56.17 -22.00 -6.50 -4.77 0.00 -56.17 -22.00 -6.50 -4.77 0.00 FORMATIVE FEEDBACK ERROR VARIANCES (compared to CORRECT ANSWERS) all should be zero SM Revenue 38.00 50.65 56.17 Gross Profit 13.56 19.56 21.47 Net Income 2.83 5.44 6.27 Cash at Bank - year end 0.40 3.19 9.31 Bank Overdraft-year end 50.120 19.201 5.280 3.115 CORRECT ANSWERS SM Revenue Gross Profit Net Income Cash at Bank - year end Bank Overdraft - year end 56.17 22.00 6.50 4.77 0.00 38.00 13.56 2.83 0.40 0.00 50.65 19.56 5.44 3.19 0.00 56.17 21.47 6.27 9.31 0.00 50.12 19.20 5.28 3.11 0.00 Do not include this Formative Feedback section in your Word or Power Point. Do not delete this Formative Feedback it will be used by Tutor for marlinal Manufacturing Setup Calculations Direct Materials Usage Budget for Year Ended 30th June 2022 Total Setup Hours Casual Table 1,200 12,000 0.20 Number of setups required for year) Total setup hours expected Total Setup labour hours (per indivdual unit produced) Deluxe Table 250 3,000 0.30 Materials Granite 15,000 Red Oak Physical Units Budget: Direct Materials required for Casual Tables Direct Materials required for Deluxe Tables Total quantity of DM to be used in production Manufacturing Overhead Costs Budget for Year Ended 30th June 2022 720,000 b.f 120,000 b.f 840,000 b.f 360,000 sq.ft. 80,000 sq.ft. 440,000 sq.ft. $ $ $ Manufacturing Operations Overhead Costs Variable costs (based on DML hours): Supplies Indirect manufacturing labour Power Maintenance 1,500,000 1,680,000 2,100,000 1,200,000 490,000 Cost Budget: Available from beginning direct materials inventory (under FIFO cost-flow assumption, as given) Red Oak Granite To be purchased and used this period: Red Oak Granite Total costs of DM to be used this period 600,000 5,390,000 3,800,000 $4,400,000 Fixed costs: Depreciation Supervision $10,280,000 Power Maintenance 1,020,000 390,000 630,000 480,000 $5,880,000 Total manufacturing operations overhead costs 9,000,000 Direct Materials Purchases Budget for Year Ended 30th June 2022 $ Materials Granite Machine Setup Overhead Costs Variable costs (based on setup hours): Supplies Total Indirect manufacturing labour Power Red Oak 390,000 840,000 90,000 Physical Units Budget: To be used in production (from DM Usage budget) Add target ending inventory Total requirements Deduct beginning inventory on hand Purchases of DM to be made (units) 840,000 b.f. 80,000 b.f. 920,000 b.f. 70,000 b.f. 850,000 b.f. 440,000 sq.ft 20,000 sq.ft 460,000 sq.ft 60,000 sq.ft 400,000 sq.ft Fixed costs: Depreciation Supervision Power 603,000 1,050,000 27,000 Total manufacturing setup overhead costs 3,000,000 $5,950,000 Cost Budget: Red Oak @ $7 per b.f Granite @ $10 per sq. ft. Total cost of DM to be purchased this year $4,000,000 $4,000,000 Total manufacturing overhead costs 12,000,000 $5,950,000 $9.950,000 The following data is available for the 2022 budget: Non Manufacturing Costs Direct Materials Red Oak Granite $1,024,000 6.50% $1,330,000 Direct manufacturing labour $7 per board foot (b.f.) (same as in 2021) Product design - fixed cost only $10 per square foot (sq.f.) (same as in 2021) Marketing costs: Variable (for all products: as a percentage of revenue) $20 per hour Fixed Distribution costs: Content of Each Finished Product Unit Variable (per cubic foot for all products) Casual Table De luxe Table Fixed 12 12 6 8 Shipping details: 4 6 Package shipping size (per product unit) (cubic feet) $2 $1,596,000 Red Oak (board feet) Granite (square feet) Direct manufacturing labour (hrs) Casual Table 18 Deluxe Table 24 Finished Product Inventories and Sales Revenues Budget for the Year Ended 30th June 2022 Casual Table Deluxe Table Units Expected sales in units Selling price Target ending inventory (in units) Beginning inventory (in units) Beginning inventory (in dollars) 50,000 S600 11,000 1,000 $384.000 10,000 Product $800 500 Casual Table 500 Deluxe Table $262.000 Total Selling Price $ 600 800 50,000 10,000 Total Revenues $ 30,000,000 8,000,000 38.000.000 Red Oak b.ft. Granite sq.f. Production (units) Budget for Year Ended 30th June 2022 Beginning inventory 70,000 60,000 Target ending inventory 80,000 20,000 Product Casual Deluxe Manufacturing Overhead Costs & Activities Allocations Budgeted sales (in units) 50,000 10,000 Plus targeted ending inventory 11,000 500 Mfg Operations Mfg Setup Activity Costs Pool Costs Pool Total required units: 61,000 10,500 Manufacturing - Variable costs (cost per unit of cost driver) Supplies $5 $26 Less opening inventory (units) on hand 1,000 500 Indirect Mfg labour S6 556 Units of finished goods to be produced 60.000 10.000 S7 S6 Maintenance $4 so Direct Manufacturing Labour Costs Budget for Year Ended 30th June 2022 Power DML Hours per Unit Total DML Hours Cost driver for variable (and fixed) costs: Mfg labour hours Hourly Wage Rate Setup labour hours Product Units produced 60,000 4 6 Total Cost $ 4.800,000 1,200,000 6.000.000 Manufacturing - Fixed costs: Depreciation Supervision Power Maintenance $20 $20 10,000 240,000 60.000 300,000 Casual Tables $603,000 Deluxe Tables $1,050,000 Total DML hours and cost $27,000 SO $1,020,000 $390,000 $630,000 $480,000 Manufacturing Setup Activity Number of product units produced per batch Setup time (hrs) per batch Casual Table 50 10 Deluxe Table 40 12 Cost of Goods Sold Budget for Year Ending 30th June 2022 Unit Cost Calculations for Ending Inventory of Finished Goods Sourcing Budget Total $ $ Provided Info 646,000 Unit of Input 7 10 20 30.0 200.0 Input Component Red oak Granite Direct manufacturing labour Manufacturing operations overhead Machine setup overhead Total Unit Cost per item Product Casual Tables Deluxe Tables Opening Finished goods as at 1st July 2021 Input Total Volume of Total Units Value $ Input Units Value $ Add Cost of goods manufactured: 12 b.f. 84 12 b.f. 84 Direct materials used 6 sq.ft 60 8 sq.ft. 80 Direct manufacturing labour 4 hrs 80 6 hrs 120 Manufacturing overhead incurred 4 hrs 120 6 hrs 180 Total cost of goods manufactured 0.20 hrs 40 0.30 hrs 60 Cost of goods available for sale 384 524 Deduct Closing finished goods as at 30th June 2022 Cost of goods sold DM DML 10,280,000 6,000,000 12,000,000 Mfg OH 28,280,000 28,926,000 4,486,000 24,440,000 Inv Nonmanufacturing Cost Budget for Year Ending 30th June 2022 Ending Inventories Valuation Budget as at 30th June 2022 Business Function Variable costs $ Fixed Costs $ Total $ Quantity on hand Cost per Unit Inventory Item $ Total Product design Marketing costs Distribution costs Total nonmanufacturing costs 2,470,000 2,280,000 1,024.000 1,330,000 1,596,000 Direct Materials 1,024,000 3,800,000 3,876,000 8,700,000 Red oak # 80,000 20,000 $7 $10 560,000 200,000 Granite # 760,000 Budgeted Operating Income Statement for Year Ending 30th June 2022 WIP (Negligible amounts, as advised) Nil Supporting Budget $ $ Finished Goods Casual tables Deluxe tables # 11,000 500 $384 $524 4.224.000 262,000 Rev COGS # 38,000,000 24.440.000 13,560,000 4,486,000 Revenue Less Cost of goods sold Gross margin Less Operating Expenses Product design Marketing Distribution Total value of Ending Inventories 5.246,000 Non Mfg Non Mfg Non Mfg 1,024,000 3,800,000 3,876,000 [# Note that under the FIFO cost flow assumption we are valuing closing inventories on hand at only the current year unit costs, as usage/sales of the inventory in the current budgeted year is greater than the opening inventory levels. 8,700,000 4.860.000 Operating Income (pre tax) not add rows or colums or change the structure/layout of this worksheet. sensitivity analysis change the GREEN cells Paste values from the Live Case into these five scenarios Base case No chairs No sales uplifts No closing inventory Chairs to meet Strat. Plan Profit Live case These are input cells that drive through the whole of the budget model 115,000 S110 0 SO 115,000 $110 115,000 $110 $110 INPUTS Chairs: Full year units sold Chairs: Sell price Casual Tables sales uplift Deluxe Tables sales uplift Ending inventory (units): Casual Tables Deluxe Tables Chairs 4.0% 3.0% 0.0% 0.0% 0.0% 0.0% 4.0% 3.0% 4.0% 3.0% 11,000 500 10,000 11,000 500 0 11,000 500 10,000 OOO 11,000 500 10,000 56.17 22.00 6.50 4.77 These are output cells that need to come through from the budget model paste this value into the table paste this value into the table paste this value into the table paste this value into the table paste this value into the table OUTPUTS SM Revenue Gross Profit Net Income Cash at Bank - year end Bank Overdraft - year end DIFFERENCES TO BA SE CASE SM Revenue Gross Profit Net Income Cash at Bank - year end Bank Overdraft - year end PATCH Cells E29 to E33 should be formulas (not values). To patch: copy cells G29:G33 and paste into cells E29:E33 -56.17 -22.00 -6.50 -4.77 0.00 -56.17 -22.00 -6.50 -4.77 0.00 -56.17 -22.00 -6.50 -4.77 0.00 -56.17 -22.00 -6.50 -4.77 0.00 FORMATIVE FEEDBACK ERROR VARIANCES (compared to CORRECT ANSWERS) all should be zero SM Revenue 38.00 50.65 56.17 Gross Profit 13.56 19.56 21.47 Net Income 2.83 5.44 6.27 Cash at Bank - year end 0.40 3.19 9.31 Bank Overdraft-year end 50.120 19.201 5.280 3.115 CORRECT ANSWERS SM Revenue Gross Profit Net Income Cash at Bank - year end Bank Overdraft - year end 56.17 22.00 6.50 4.77 0.00 38.00 13.56 2.83 0.40 0.00 50.65 19.56 5.44 3.19 0.00 56.17 21.47 6.27 9.31 0.00 50.12 19.20 5.28 3.11 0.00 Do not include this Formative Feedback section in your Word or Power Point. Do not delete this Formative Feedback it will be used by Tutor for marlinal