Tichina Scales S E AutoSave A 8 - = scales_Miller-Nobles_6e-12e-Using Excel_Ch22_Start - Protected View - Excel The Home Insert Draw P age Layout Formulas Data Review View Help Search PROTECTED VIEW Be careful tiles from the Internet can contain viruses. Unless you need to edit, it's safe to stay in Protected View Enable Iditing A1 X MASTER BUDGETS F A B C D E T J K L M N O P R S T 9 Requirements Possible Points 10 1. Prepare a Sales Budget 11 2 . Prepare a Production Budget. 12 3. Prepare a Direct Materials Budget 13 4. Prepare a Direct Labor Budget. 5. Prepare a Manufacturing Overhead Budget. 15 6. Prepare a Cost of Goods Sold Budget. 7. Prepare a Selling and Administrative Expense Budget. 14 S88 88 16 18 19 Excel Skills 20 1. Create formulas with cell references 2. Use the ROUND function, 4 Saving & Submitting Solution 5 1 Save file to desktop. a. Create folder on desktop, and label COMPLETED EXCEL PROJECTS 2 Upload and submit your file to be graded. a. Navigate back to the activity window screen where you downloaded the initial spreadsheet b. Click Choose button under step 3; locate the file you just saved and click Open c. Click Upload button under step 3 d. Click Submit button under step 4 Instructions ENTERANSWERS scales, Noble File Home insert Draw P age Layout Formulas Data Review Vi Telp Search PROTECTED VIEW calut files from the internet can contain vies. Unless you need to edit it's safe to say i Protected View Enable Editing Al - X Thunder Creek Company expects sales of 10,000 units in January 2018, 24,000 units in February 30,000 units in March, 34,000 in April, and 36,000 in May EI huary 2018, M,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 Thunder Creek Company expects sales of 18,000 units in Prepare a sales budget. 2018 2. 01 Total April May Jan Feb Mar 4 Budget : Sales Budget 5 Budgeted units to be sold 6 Sales price per unit 7 Total Sales o Thunder Creek wants to finish each month with 20% of next month's sales in units. 10 Prepare a production budget. (When entering answers in the production budget, use the sales budget for your cell references. Enter all values as positive without a minus sign-in row 11 Hint: Beginning inventory for the period is equal to the ending inventory of the previous period. 12 2018 Dec lan 01 Total Feb 14 Budget #2: Production Budget April May 15 Budgeted units to be sold 16 Plus: Desired units in ending inventory 17 Total units needed Less: Units in beginning inventory 19 Budgeted units to be produced 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 Raw Materials inventory Management desires an ending inventory of 25% of next month's materials requirements Prepare a Direct Materials Budget. (When entering answers in the direct materials budget, use the production budget for your cell references. Enter all values as positive without an Budget #3: Direct Materials Budget Instructions ENTERANSWERS Feb 2018 Mar 01 Total April Tichina Scales 5 - Excel 0 Comm Share File sales, Milan Nobles_6e-12-Using Excel Ch22_Start Protected View Home insert Dow P age Layout Formulas Data Review View Help Search PROTECTED VIEW Becast s from the were can contain viruses. Unless you need to it. It's safe to stay in Protected Vow Enable Iditing A1 - X Thunder Creek Company expects sales of 1,000 units in ianuary 2011, 24,000 units in February 30,000 units in March, M,000 in April, and 36,000 in May. The sales price is $ 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 21 material in Raw Materials Inventory Management desires an ending inventory of 25% of next month's materials requirements 22 Prepare a Direct Materials Budget. (When entering answers in the direct materials budget, use the production budget for your cell references. Enter all values as positive without a minus sign-in row 31.) 01 Total April 25 Budget : Direct Materials Budget 26 Budgeted units to be produced 27 Direct materials (pounds) per unit 28 Direct materials needed for production 29 Plus Desired direct materials in ending inventory (pounds) 30 Total direct materials needed 31 Less Direct materials in beginning inventory (pounds) 32 Budgeted purchase of direct materials 13 Direct material cost per pound 34 Budgeted cost of direct materials purchases > Thunder Creek Company's workers require 30 minutes of labor to produce each unit of product. The labor cost is $20 per hour 37 Prepare a Direct Labor Budget. (When entering answers in the direct labor budget, use the direct material budget for your cell references) 19 Budgets Direct Labor Budget 40 Budgeted units to be produced 41 Direct labor hours per unit 42 Direct labor hours needed for production 43 Direct labor cost per hour 44 Budeeted direct labor cost instructions ENTERANSWERS Protected View Excel Tiching Scales " File Home sert PROTECTED VW Becu cale M -Noblesse 12e-Using ExcelC22_Start Draw Page Layout Formulas Data Review View Help Search estrom the Internet can continues this you need to its were stay in Protected View Enable Editing A1 - Thunder Creek Company expects sales of 1 0 units in January 2018, 24,000 units in February 10,000 units in March 14,000 in April, and 36,000 in May. The 15 Thunder Creek Company's workers require 30 minutes of labor to produce each unit of product. The labor costi 5.0 per hour 37 Prepare a Direct Labor Budget.When entering answers in the direct labor budget, use the direct materials budget for your cell references.) Mar 1 Total 39 Butt Direct Labor Budget 40 Budgeted units to be produced 41 weet bor hours per unit Direct labor hours needed for production Direct labor cost per hour B yted direct labor cost Thunder Creek Company prepares its Manufacturing Overhead Budget for each dret labor hour the variable overhead costs are 46 indirect Material - $1.00 per indirect labor cost $1.30 per DUH: Maintenance = $1.20 per 7 The Fixed Overhead Costs per month : Salaries of S40.000 Depreciation $20,000 and Maintenance $10.000 Prepare a Manufacturing Overhead Budget.When entering answers in the manufacturing overhead budget, use the direct laber budget for your references) 49 Use'ROUND Runction to round the predetermined overhead allocation rate to two decimal places. Manufacturing overhead is allocated using direct labor hours. MT 52 Budget: Manufacturing Overhead Budget Budgeted us to be produced 34 VOH cost per unit 55 Buded VON 56 Budyted FOH 57 Depreciation Total budgeted TOH INTERANSWERS Excel Tichina sees - scales Miler-hobiel de-12e-Using Excel 0432_Stat Protected View Review View Help Search File Home insert Dr Page Layout formulas Data Thunder Creek Company expect sales of 1,000 units in January 2018, M u nits in February 6,000 units in March, in April, and 16,000 in May. The sales price M 1 Total 52 Bus: Mancing whead Bodiet 53 Budgeted units to be produced 54V cost per unit 55 Budgeted VOH 56 budgeted FOH 57 Depreciation 59 Total budgeted FOH 50 Budeteanufacturing overhead costs 6 Direct labor hours 63 predetermined overhead location rate per 65 hader Creek Company the first in stato ventory costing method 66 The Beginning Finished Goods inventory is 100 contgof. 67 begin by calculating the projected cost to produce each unit in 2018 based on projected sales. (Hint in Cost per unit table, cell references come from Direct Materials, Drect Labor, and Manufacturing Om 6 U ROUND function to round the fixed manufacturing overhead cost per unit to two decimal places Prepare a cost of Goods Sold Budget (Hint: Units per month calculated using cell references to both sales budget and production budget) 72 Direct labor cost per unit 73 Manufacturing overhead to per unit 74 Tots projected manufacturing cost per unit INTERANSWERS Tichina Scales 3 - Excel 0 Comm. Share scales, Midle-Nobles, 6e-12-Using Excel Ch22 Start - Protected View Revita vin hel e search Data Enable Editing File Home insent Drais Page Layout formulas PROTECTED VW Be careful lestrom the Internet can contains . Unless you need to edit it's safer to stay in Protected View AL - X Thunder Creek Company expects sales of 1,000 units in January 2018, 24,000 units in February, 30,000 units in March, 34,000 in April, and 15,000 in May. The sales price is 69 Prepare a cost of Goods Sold Budget. (Hint: Units per month calculated using cell references to both sales budget and production budget.) Cost per unit 71 Direct matecial cost per unit 72 Direct labor cost per unit 73 Manufacturing overhead cost per unit 74 Total projected manufacturing cost per unit Mar 77 Budget Cost of Goods Sold Budget 78 beginning Finished Goods inventory, 3,600 units. 79 Units produced and sold in 2018 30 Cost per unit 81 Units per month 12 Total cost of units produced and sold in 2018 Total budgeted cost of goods sold Thunder Creek Company's viate supplies expense per months 53.00 per unit. The fixed selling and administrative expenses per month consist of Sales 35 5245 000. Advertising 530,000, and Depreciation $28.000 16 Prepare a Seling and Administrative Expense Budget When entering answers in the selling and administrative budget use the sales budget for your cell references Feb Mar 01 Total Budget: Selling and Administrative Expense Budget 89 Salaries expense Advertising expense 91 Depreciation expense 92 Supplies expense that canca instructions ENTERANSWERS File Home Insert Draw Page Layout Formulas Data Review View Help Search PROTECTED VIEW Be careful files from the Internet can contain viruses. Unless you need to edit it's safe to stay in Protected View Enable Editing - 1 x 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 Cost per unit Units per month Total cost of units produced and sold in 2018 1 Total budeted cost of goods sold 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: $245,000. Advertising $30,000, and Depreciation: $28,000 Prepare a Selling and Administrative Expense Budget (When entering answers in the selling and administrative budget, use the sales budget for your cell references.) 2018 Budget 7: Selling and Administrative Expense Budget 91 Total salaries expense Advertising expense Depreciation expense Supplies expense Total budgeted SEA expense ZS29251 instructions ENTERANSWERS