using this format. show work plz
Chapter 27 Using Excel P22-56 Using Excel to prepare an operating budget (manufacturing company) Thunder Creek Company is preparing budgets for the first quarter of 2018. All relevant information is presented on the Excel template. Requirements 1. Prepare a Sales Budget 2. Prepare a Production Budget. 3. Prepare a Direct Materials Budget 4. Prepare a Direct Labor Budget. 5. Prepare a Manufacturing Overhead Budget. 6. Prepare a cost of Goods Sold Budget. 7. Prepare a Selling and Administrative Expense Budget. 19 3 4 5 6 Excel Skills 1. 8 2. Create formulas with cell references. Use the ROUND function. May 1 BUDGETS 2 Thunder Creek Company expects sales of 18,000 units in January 2018, 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. 3. Create a sales budget. 4 5 2018 6 Budget #1: Sales Budget Jan Feb Mar 01 Total 7 Budgeted units to be sold April May 8 Sales price per un 9 Total Sales 10 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 2017 2018 16 Budget 2: Production Budget Dec Jan Feb Mar 91 Total April 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 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 material in law 24 Materials Inventory Management desires an ending inventory of 25% of next month's materials requirements 25 25 2018 27 Budget 13: Direct Materials Budget Jan Feb Mar Q1 Total April 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 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 2018 41 Feb Mar QI Total 42 Budget 14 Direet Labor Budget 43 Budgeted units to be produced 44 Direct labor hours per unit 45 Cirect labor hours needed for production C. D 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 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 2018 42 Budget 4: Direct Labor Budget Jan Feb Mar 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 47 Budgeted direct labor cost 48 Q1 Total 1. Thunder Creek Company prepares its Manufacturing Overhead Budget. For each direct labor hour, the variable overhead costs are 50 Indirect Materials - $1.00 per DLH: Indirect labor Cost = $1,30 per Maintenance $1.20 per 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 2018 57 Budget #S: Manufacturing Overhead Budget lan Feb Mar QI Total 58 Budgeted units to be produced 59 VOH cost per unit 60 Budgeted VOH 61 Budgeted FOH Depreciation 63 Salaries and maintenance 64 Total budgeted FOH 65 Budgeted manufacturing overhead costs 66 67 Direct labor hours (4) 68 Predetermined owerhead allocation rate per Du 69 70 71 72 Thunder Creek Company uses the first in, first out (FIFO) inventory costing method 73 The Beginning Finished Goods Inventory is $16.400 consisting of 1,600 units. 74 65 Budgeted manufacturing overhead costs 66 67 Direct labor hours (DLHR) 68 Predetermined overhead allocation rate per DLH 69 70 71 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 2018 based on projected sales. 76 2. ROUND the fixed manufacturing overhead cost per unit to two decimal places. 77 78 Cost per unit 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 2018 86 Budget 16: Cost of Goods Sold Budget Jan Feb Mar Q1 Total 87 Beginning Finished Goods Inventory, 3.600 units. 88 Units produced and sold in 2018 89 Cost per unit 90 Units per month 91 Total cost of units produced and sold in 2018 92 Total budgeted cost of goods sold 93 94 Thunder Creek Company's variable supplies expense per month is $300 per unit. The fixed selling and administrative expenses per month consist of Salaries 95 $245,000 Advertising $30,000, and Depreciation $28,000 96 2018 197 Jan Feb Mar Qi Total 98 Budget 117: Selling and Administrative Expense Budget 99 Salaries expense 100 Advertising expense 101 Depreciation expense 102 Supplies expense 103 Total budgeted S&A expense 104 105 106 Feb Mar 1 BUDGETS 2 Thunder Creek Company expects sales of 18,000 units in January 2018, 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. 3 Create a sales budget. 4 5 2018 6 Budget #1: Sales Budget Jan 01 Total April May 7 Budgeted units to be sold 8 Sales price per unit 9 Total Sales 10 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 2017 2018 16 Budget 2: Production Budget Dec Jan Feb Mar Q1 Total April May 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 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 material in 24 Raw Materials Inventory. Management desires an ending inventory of 25% of next month's materials requirements 25 26 2018 27 Budget 113: Direct Materials Budget Jan Feb Mar Q1 Total April 28 Budgeted units to be produced Instructions Budgets 21 Budgeted units to be produced 22 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 material in 24 Raw Materials Inventory. Management desires an ending inventory of 25% of next month's materials requirements 25 26 2018 27 Budget 13: Direct Materials Budget lan Feb Mar Q1 Total April 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 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 2018 42 Budget #4: Direct Labor Budget Jan Feb Mar Q1 Total 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 47 Budgeted direct labor cost 48 Intructions Budgets B G 56 46 Direct labor cost per hour 47 Budgeted direct labor cost 48 49 1. Thunder Creek Company prepares its Manufacturing Overhead Budget. For each direct labor hour, the variable overhead costs are: SO 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 2018 57 Budget #5: Manufacturing Overhead Budget Jan Feb Mar Q1 Total 58 Budgeted units to be produced 59 VOH cost per unit 60 Budgeted VOH 61 Budgeted FOH 62 Depreciation 63 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 72 Thunder Creek Company uses the first-in, first-out (FIFO) inventory costing method. G105 X f A D F G E 71 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 2018 based on projected sales. 76 2. ROUND the fixed manufacturing overhead cost per unit to two decimal places. 77 78 Cost per unit 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 2018 86 Budget #6: Cost of Goods Sold Budget Jan Feb Mar Q1 Total 87 Beginning Finished Goods Inventory, 3,600 units. 88 Units produced and sold in 2018 89 Cost per unit 90 Units per month 91 Total cost of units produced and sold in 2018 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 2018 98 Budget #7: Selling and Administrative Expense Budget Jan Feb Mar Q1 Total Budgets Instructions 90 Units per month 91 Total cost of units produced and sold in 2018 92 Total budgeted cost of goods sold 93 Thunder Creek Company's variable supplies expense per month is $3.00 per unit. The fixed selling and administrative expenses per month consist of Salari 95 $245,000; Advertising: $30,000, and Depreciation: $28,000 96 97 2018 98 Budget #7: Selling and Administrative Expense Budget Jan Feb Mar Q1 Total 99 Salaries expense 100 Advertising expense 101 Depreciation expense 102 Supplies expense 103 Total budgeted S&A expense 104 105 106 107 108 109 110 111 112
