1 Chapter M:7 2 Using Excel 3 Using Excel to prepare an operating budget (manufacturing company) 4 5 Thunder Creek Company is preparing budgets for the first quarter of 2024. All relevant information is presented on the Excel template. 6 7 Requirements Prepare a Direct Materials Budget. 8 1. Prepare a Sales Budget. 9 2. Prepare a Production Budget. 10 3. 11 4. Prepare a Direct Labor Budget. 12 13 14 15 5. 6. 7. Prepare a Manufacturing Overhead Budget. Prepare a Cost of Goods Sold Budget. 16 Excel Skills Prepare a Selling and Administrative Expense Budget. 17 1. 18 2. 19 20 Create formulas with cell references. Use the ROUND function. J BUDGETS BUDGETS 2 Thunder Creek Company expects sales of 18,000 units in January 2024, 24,000 units in February, 30,000 units in March, 34,000 in April, and 36,000 in May. The sales price is $34 per unit. Create a sales budget. 4 5 6 Budget 1: Sales Budget 7 Budgeted units to be sold Sales price per unit 9 Total Sales 10 + 2024 Jan Feb Mar Q1 Total April May 11 Thunder Creek wants to finish each month with 20% of next month's sales in units 12 Create a production budget. 13 Hint: Beginning inventory for the period is equal to the ending inventory of the previous period. 14 15 16 Budget 2: Production Budget 17 Budgeted units to be sold 18 Plus: Desired units in ending inventory 19 Total units needed 20 Less: Units in beginning inventory 21 Budgeted units to be produced 22 23 2023 2024 Dec Jan Feb Mar Q1 Total April May Thunder Creek Company uses 2 pounds of direct materials for each unit it produces, at a cost of $4.00 per pound. The company begins the wear with 24 material in Raw Materials Inventory Management desires an endine inventor of w Instruction A1 X BUDGETS A 22 B D G H 23 Thunder Creek Company uses 2 pounds of direct materials for each unit it produces, at a cost of $4.00 per pound. The company begins the year with 9,500 pounds of 24 material in Raw Materials Inventory. Management desires an ending inventory of 25% of next month's materials requirements 25 26 27 Budget #3: Direct Materials Budget 28 Budgeted units to be produced 29 Direct materials (pounds) per unit 30 Direct materials needed for production 31 Plus: Desired direct materials in ending inventory (pounds) 32 Total direct materials needed 33 Less: Direct materials in beginning inventory (pounds) 34 Budgeted purchase of direct materials 35 Direct material cost per pound 36 Budgeted cost of direct materials purchases 37 38 2024 Jan Feb Mar Q1 Total April 39 Thunder Creek Company's workers require 30 minutes of labor to produce each unit of product. The labor cost is $20 per hour 401 41 42 Budget #4: Direct Labor Budget 43 Budgeted units to be produced 44 Direct labor hours per unit 45 Direct labor hours needed for production Budgets Instructions Ready 2024 Jan Feb Mar Q1 Total E G 38 39 Thunder Creek Company's workers require 30 minutes of labor to produce each unit of product. The labor cost is $20 per hour 40 41 42 Budget #4: Direct Labor Budget 43 Budgeted units to be produced 44 Direct labor hours per unit 45 Direct labor hours needed for production 46 Direct labor cost per hour 2024 Jan Feb Mar Q1 Total 47 Budgeted direct labor cost 48 49 50 1. Thunder Creek Company prepares its Manufacturing Overhead Budget. For each direct labor hour, the variable overhead costs are: Indirect Materials $1.00 per DLH; Indirect Labor Cost $1.30 per DLH; Maintenance $1.20 per DLH 51 2. The Fixed Overhead Costs per month are: Salaries of $40,000, Depreciation $20,000 and Maintenance - $10,000. 52 3. ROUND the predetermined overhead allocation rate to two decimal places. Manufacturing overhead is allocated using direct labor hours. 53 54 55 56 57 Budget #5: Manufacturing Overhead Budget 58 Budgeted units to be produced 59 VOH cost per unit 60 Budgeted VOH 61 Budgeted FOH Budest 2024 Jan Feb. Mar Q1 Total 49 C D E F 1. Thunder Creek Company prepares its Manufacturing Overhead Budget. For each direct labor hour, the variable overhead costs are: Indirect Materials $1.00 per DLH; Indirect Labor Cost $1.30 per DLH; Maintenance-$1.20 per DLH 50 51 2. The Fixed Overhead Costs per month are: Salaries of $40,000, Depreciation $20,000 and Maintenance $10,000. 52 3. ROUND the predetermined overhead allocation rate to two decimal places. Manufacturing overhead is allocated using direct labor hours. 53 54 55 56 57 Budget #5: Manufacturing Overhead Budget 58 Budgeted units to be produced 59 VOH cost per unit 60 Budgeted VOH 61 Budgeted FOH 62 63 Depreciation Salaries and maintenance 64 Total budgeted FOH 65 Budgeted manufacturing overhead costs 66 67 Direct labor hours (DLHr) 68 Predetermined overhead allocation rate per DLH 69 70 71 2024 Jan Feb Mar Q1 Total 72 Thunder Creek Company uses the first-in, first-out (FIFO) inventory costing method. 73 The Beginning Finished Goods Inventory is $86,400 consisting of 3,600 units. 74 75 1. Begin by calculating the projected cost to produce each unit in 2024 based on projected sales. 76 2. ROUND the fixed manufacturing overhead cost per unit to two decimal places, 77 78 79 Direct material cost per unit 80 Direct labor cost per unit 81 Manufacturing overhead cost per unit 82 Total projected manufacturing cost per unit 83 84 85 86 Budget #6: Cost of Goods Sold Budget 87 Beginning Finished Goods Inventory, 3,600 units. 88 Units produced and sold in 2024 89 Cost per unit 90 Units per month 91 Total cost of units produced and sold in 2024 92 Total budgeted cost of goods sold Cost per unit 2024 Jan Feb Mar Q1 Total 93 94 ol manufacturing overhead cost per unf 82 Total projected manufacturing cost per unit 83 84 B C D E F G 85 86 Budget #6: Cost of Goods Sold Budget 2024 Jan Feb Mar Q1 Total 87 Beginning Finished Goods Inventory, 3,600 units. 88 Units produced and sold in 2024 89 Cost per unit 90 Units per month 91 Total cost of units produced and sold in 2024 92 Total budgeted cost of goods sold 93 94 Thunder Creek Company's variable supplies expense per month is $3.00 per unit. The fixed selling and administrative expenses per month consist of Salaries 95 $245,000, Advertising $30,000, and Depreciation $28,000 96 97 98 Budget #7: Selling and Administrative Expense Budget 99 Salaries expense 100 Advertising expense 101 Depreciation expense 102 Supplies expense 103 Total budgeted S&A expense 104 2024 Jan Feb Mar Q1 Total