Thunder Creek Company is preparing budgets for the first quarter of 2018. All relevant information is presented on the Excel template. Prepare a sales budget. ( 18pts) Thunder Creek Company expects sales of 18,000 units in January 2018,24,000 units in Eebruary, 30,000 units in March, 34,000 in April, and 36,000 in May. The sales price is $34 per unit. (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.) Prepare a production budget. ( 26 pts Thunder Creek wants to finish each month with 20% of next month's sales in units. Remember, beginning inventory for the period is equal to the ending inventory of the previous period. (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.) Prepare a direct materials budget. ( 39pts) 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. (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.) Prepare a direct labor budget. (20 pts) Thunder Creek Company's workers require 30 minutes of labor to produce each unit of product. The labor cost is $20 per hour. (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.) Prepare a Manufacturing Overhead Budget. (33 pts) 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 2. The Fixed Overhead Costs per month are: Salaries of $40,000, Depreciation =$20,000 and Maintenance =$10,000. 3. ROUND the predetermined overhead allocation rate to two decimal places. Manufacturing overhead is allocated using direct labor hours. (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.) Calculate Cost Per Unit, then prepare a cost of goods sold budget. (20 pts) Thunder Creek Company uses the first-in, first-out (FIFO) inventory costing method. The Beginning Finished Goods lliventory is $86,400 consisting of 3,600 units. 1. Begin by calculating the projected cost to produce each unit in 2018 based on projected sales. 2. ROUND the fixed manufacturing overhead cost per unit to two decimal places. (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.) Prepare a Selling and Administrative Expense Budget. (20 pts) 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 (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.) Once completed, save the project to the desktop. File name can be anything, but DO NOT include spaces. Return to auto-graded excel activity window, and progress to Step 3. Follow on screen instructions. (To review score, navigate to Results in MYLab and click on Review link.) Thunder Creek wants to finish each month with 20% of next month's sales in units. 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-io row 18. Hint: Beginning imventory for the period is equal to the ending imventory of the previous period Thunder Creek Compary uses 2 pounds of direct materials foc each unit it produces, at a cost of $400 per pound. The compuey begirs the year with 9,500 pounds of materlal in Raw Materials Inventory. Managensent desires an ending inventory of 25s of neit mopth's materials requirements Prepare a Direct Materials Budget. (When entering ardwers in the direct materials budget, use the production budget for your cell teferences. Enter all values as positive-without a minus sign-in row 31 .) Budget w3: Direct Materials budget Budceted units to be producted Direct materials (poundi) per unit Direct materials needed for production Fin: Desired direct mutecials in ending imentory (poundy) Tocal direct materials needed Less. Drect materals is beginning inventory (pounds) fludgeted purchase of drect materialy Drect materiat cost per pound budgeted cost of derect materials purchatei Thunder Creek Company w worken require 30 minules of 4 bog to produce each unit of produet. The laber cost is 520 per hour Prepare a Duect Labor Budget. (When emering amwen in the direct labor budeet, use the direct materals budget for your cell referencei.) thudget B4i Birect Lber bulket Budeteted units to be produced 41. Direct libor hours per unit 42. Direct labor toun needes for prosuction 43 Direct labar cost per hour Budetred direct laber cost hunder Creek Company prepares its Manufacturing Overhead Budget. For each direct labor hour, the variable overhead costs are: Indirect Materials =$1.00 per DUH; Indirect Labor Cost =$1.30 per OLH; Maintenance =$1.20 per DLH The Fixed Overhead Costs per month are: Salaries of $40,000, Depreciation =$20,000 and Maintenance =$10,000. Prepare a Manufacturing Overhead audget. (When entering answers in the manufacturing overhead budget, use the direct labor budget for your cell references.) Use '=ROUNO' function to round the predetermined overhead allocation rate to two decimal places. Manufacturing overhead is allocated using direct labor hours. der Creck Compary usei the fint-in, first-out (HFO) itventory cottine method Bezinnire Finiahed Goode invertory in $85,400 consisting of 3,600 unith. ie legONO function to tound the fieed matularturing ovethead cost per unit to two declmal places. epare a Coot of Goodr Sold butcet .Derti Unis per month calculated uvine cell merences to both waler budert and preduction buget.) bulevt =6: Cost of Goods sold budeet Begirnize Finshied Goods lvemtory, Moo units. Units produced and sold in 2ous Cost per usit Units per morth Total cont of uvits produces and solid in 2018 Total bustreted east of eocdi sold Adverting 330.060, =nt Oeprecioson 13t, 000