Can anyone tell me if I'm doing this correctly and possibly help me out with the rest? Thanks in advance! Also, the sales price per unit should be $65, not $35.

AutoSave OCH CH22_Kenley Hatin Search (Alti) Kenley Hatin KM 1 File Home Insert Page Layout Formulas Data Review View Help Comments Share X = = = 25 General Conditional Formatting Insert Calibri FO - 11 - A A LD = $ % 9 Formal as Table Delete Paste Analyze Data B 1 u 63 Cel Styles Format Clinic Famil Aiv Nur Styles cs Frating Andysis L23 3 fo D E F G H 1 M N 0 P a R S T U V w > 1 MASTER BUDGETS 2 Using Excel to prepare an operating budget (manufacturing company) 3 4 Thunder Creek Company is preparing budgets for the first quarter of 2018. All relevant information is presented on the Excel template. 5 Use the blue shoded areas on the ENTER-ANSWERS tab for inputs. 6 Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab you will be marked wrong. Enter all amounts as positive values. Do not use a minus sign or parentheses for any va 7 8 9 Requirements 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. 14 5. Prepare a Manufacturing Overhead Budget 15 6. Prepare a Cost of Goods Sold Budget 16 7. Prepare a Selling and Administrative Expense Budget 17 18 19 Excel Skills 20 1. Create formulas with cell references, 21 2. Use the ROUND function. 22 231 24 Saving & Submitting Chapter Excel Assignments 25 1. Save file 26 a. You may want to create folder on desktop, and label COMPLETED EXCEL PROJECTS 27 b. Name your files: CHnumber_FirstNameLastName (e.g., CH1_RobynVerdery). 28 2. Upload and submit your file to be graded, 29 a. Click on "Assessments" located on the navigation bar at the top of the page to open the dropdown menu. 30 b. Click on "Assignments." 31 C. Select the assignment for this chapter. 32 d. Upload your file(s). 33 e. Double check you uploaded the right files. Instructions ENTERANSWERS Ready Accessibility: Good to go 100% # BH I Type here to search OL O AOOK 1:36 AM 4/1/2022 AutoSave OCH CH22_Kenley Hatin Search (Alti) Kenley Hatin KM 1 File Home Insert Page Layout Formulas Data Review View Help Comments Share X = = = 25 Custom Conditional Formatting Insert FO Calibri - 11 - A A WE LD = $ % 9 Format as Table Dx Delete Pasto Analyze Data B 1 A- + Cel Styles Format Cli Famil Aiy Nur Styles CH Frating Audysis C29 fo 4 A D E F . H M N 1 Thunder Creek Company expects sales of 19,000 units in January 2018, 25,000 units in February, 31,000 units in March, 35,000 in April, and 37,000 in May. The sales price is $35 per unit. 2 Prepare a sales budget. 3 2018 4 Budget #1: Sales Budget Jan Feb Mar 01 Total April May 5 Budgeted units to be sold 19,000 25,000 31,000 75,000 35,000 37,000 6 Sales price per unit 35 35 35 35 35 35 7 Total Sales 665,000 $ 875,000 S 1,085,000 S 2,625,000 S 1,225,000 $ 1,295,000 a a 9 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 18.) 11 Hint: Beginning inventory for the period is equal to the ending inventory of the previous period. 12 13 2017 2018 14 Budget 12: Production Budget Dec Jan Feb Mar Q1 Total April May 15 Budgeted units to be sold 19,000 25,000 31,000 25,000 35,000 37,000 16 Plus: Desired units in ending Inventory 3.800 5,000 6,200 7.000 18,200 7,400 17 Total units needed 24,000 31,200 38,000 93,200 42,400 19 Less: Units in beginning inventary 7,400 5,000 6,200 7,000 18,200 19 Budgeted units to be produced 16,600 26,200 31,800 86,200 24,200 20 Thunder Creek Company uses 3 pounds of direct materials for each unit it produces, at a cost of $5.00 per pound. The company begins the year with 10,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.) 23 24 25 Budget #3: Direct Materials Budget Jan Feb Mar Q1 Total April 26 Budgeted units to be produced 16,600 26,200 31,800 86,700 24,200 27 Direct materials (pounds) per unit 3.00 3.00 3.00 3.00 3.00 28 Direct materials needed for production 49,800 78,600 95,400 258,600 72,600 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 33 Direct material cost per pound 5.00 5.00 5.00 5.00 34 Budgeted cost of direct materials purchases 35 Instructions ENTERANSWERS Ready Accessibility: Good to go 2018 # + 90% BH I Type here to search Oo O g A OU 1:37 AM 4/1/2022 AutoSave OCH 2 CH22_Kenley Hatin Search (Alti) Kenley Hatin KM 2 File Home Insert Page Layout Formulas Data Review View Help Comments Share X = = = 25 Custom Conditional Formatting Insert FO Calibri 11A A LD $ % 9 Format as Table Dx Delete SOM Paste Analyze Data B 1 u A + cel Styles Format Cli Famil Algem Nur Style cals Frating Andysis C29 fo M N A B D E H 36 Thunder Creek Company's workers require 30 minutes of labor los produce euch unilul product. The labor cost is $22 per hour 31 Prepare a Direct Labor Budget. (When entering answers in the direct labor budget, use the direct materials budget for your cell references 38 2018 39 Budget #4: Direct Labor Budget Jan Feb Mar Q1 Total 40 Burgnted units to be produced 41 Direct labur hours xir unit 42 Direct labur hours needed for production 13 Direct labor cost per hour 44 Budgeted direct labor cost 45 1. Thunder Creck Company prepares its Manufacturing Overhead Budget. For each direct labar hour, the variable overhead costs are: 46 Indirect Materials = $2.00 per DLH; Indirect Labur Cost = $2.30 per DLH; Maintenance = $2.20 per DLH 47 2. The Fixed Overhead Costs per month are: Salaries at $50,000, Depreciation -$30,000 and Maintenance - $20,000. 40 Prepare a Manufacturing Overhead Budget. (When entering answers in the manufacturing overhead budget, use the direct labur budget for your cell references. 49 USROUND' function to round the prediatermined overhead allocation rate to twn derimal planas. Manufacturing overhead is allocated using direct labor hours. 50 51 2018 52 Budget #5: Manufacturing Overhead Budget Jan Feb Mar Q1 Total 3 Budgeted units to be produced 54 VOH cost per unit 55 Budgeted VOH 56 Budge led roll 57 Depreciation 58 Salaries and maintenance 59 Total budgeted FOH 60 Budgeted manufacturing overhead costs 61 62 Direct labor hours (DLH) 63 Predetermined overhead allocation rale per DLII 61 65 Thunder Creek Company uses the first-in, first-out (FIFO) inventory costing method 66 The Aeginning Finished Goods Inventory is $1.36 200 consisting of 3,800 units. 67 Begin by calculating the projected cost to produce cach unit in 2018 trascd on projected sales. (llint: In "Cost per unit" table, coil references come from Direct Matcrials, Direct Latar, and Manufacturing Overhead budgets. 61 Use 'ROUND' (unction lu round the lixed manufacturing overhead cost per unillu lwu decimal places 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 material cost per unit 72 Direct labor cast cr unit Instructions ENTERANSWERS Ready Accessibility: Good to go # + 90% BH I Type here to search O 0 g A OU 1:37 AM 4/1/2022 AutoSave OCH CH22_Kenley Hatin Search (Alti) Kenley Hatin KM 2 File Home Insert Page Layout Formulas Data Review View Help Comments Share X = = = 25 Custom Conditional Formatting Insert FO Calibri - 11 - A A LD = $ % 9 Format as Table Dx Delete SEM Paste Analyze Data B U A. + cel Styles Format Cli Famil Aiy Nun Style cals Frating Andysis C29 X f . H M N D E F 69 Prepare a Cost of Goods Sold Budget. (I lint: Units per irunth calculated using cell references to both sales budget and productiur budget.) Cost per unit 71 Direct material cost per unit 72 Direct labor cost per unit 78 Manufacturing nrrhead cost per unit 74 Total projected manufacturing cast per unit 75 76 2018 77 Budget #6: Cost of Goods Sold Budget Jan Feb Mar Q1 Total 79 Beginning Finished Goods Inventory 1,100 units. 79 Units pruducudurid sold in 2018 80 Cost per unit 81 Units per month 82 Total cost of units produced and sold in 2018 83 Total budgeted cost of goods sold Thunder Creek Company's variable supplies expense per month is $4.00 per unit. The fixed seling and administrative expenses per month consist of Salaries 85 $255,000; Advertising: $35.000, and Depreciation: $38,000 86 Prepare a Selling and Administrative Expense Budget. (When entering answers in the selling and administrative budget, use the sales budget for your cel references.) 87 2018 BA Budget #7: Selling and Administrative Expense Budget Jan Feb Mar Q1 Total 89 Salaries expense 90 Advertising expense 91 Depreciation expense 92 Supplies expense 93 Total budgeted S&A expense 94 95 96 97 9 100 101 102 103 101 105 Instructions ENTERANSWERS Accessibility: Good to go Ready # + 90 SON BH I Type here to search OL O g A OU 1:37 AM 4/1/2022