Excel Skills 1. Create formulas with cell references. 2. Use the ROUND function. 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. (18 pts) 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. (Always use cell references and formulas where appropriate to receive full credit.) 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.) Prepare a direct materials budget. (39 pts) 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.) 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.) 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.) 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 Inventory 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.) 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.) Excel Skills 1. Create formulas with cell references. 2. Use the ROUND function. 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. Prepare a sales budget. Lan Feb Mar 01 Total April May Budget 1: Sales Budget Budgeted units to be sold Sales price per unit Total Sales 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--in row 21.) Hint: Beginning inventory for the period is equal to the ending inventory of the previous period. Dec l an Feb Mar 01 Total April Budget 2: Production Budget Budgeted units to be sold Plus Desired units in ending inventory Total units needed Less: Units in beginning inventory 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 Rw Materials inventory Management des an ending investery of 29 of next month's materials requirements Prepare a Direct Materials Budget. When entering answers in the direct materiais budget use the production budget for your cell references. Enter all values as positive--without a minus sign--in row 35 1 Total Budget 3: Direct Materials Budget Budgeted units to be produced Direct materials (pounds per unit Dest materials needed for production Plus Desired direct materials in ending inventory (pounds) Tots direct mais needed Less Direct materials in beginning invertory pounds Budgeted purchase of direct materials Direct material cost per pound Budgeted cost of direct materiais purchases Thunder Creek Company's workers require 30 minutes of labor to produce each unit of product. The labor cost is $20 per hour Prepare a Direct Labor Budget. (When entering anges in the directibor budete the direct mat budet for your cell e s) Mar 01 Total Budget: Direct Labor Budget Budgeted us to be produced Directibor hours per Direct labor hours needed for production Direct labor cost per hour Budgeted vestibor cost under vrees empany prepares guverneures per enc o urte van oemes coses are Indirect $1.00 PER Indirect laber Cost = $1.20 OK Minnance $1.20 DEH The Fixed Overhead Costs per month are: Saries of San.000, Depreciation $20,000 and Maintenance = $10.000 Prepare a Minutacturing Overhead Budget. (When entering agwers in the manufacturing overhead budget we the direct laber budget for your cell references) R OUND function to round the predetermined overhead allocation rate to two decimal places. Manufacturing overhead is allocated using direct labor hours. U under vrees empany prepares guverneures. Per enc o urtevan emes coses are Indirect Materials = $1.00 per indirect laber Cast = $1.30 per Maintenance = $1,20 per The Fixed Overhead Costs per month are: Sagres of $40.000 Depreciation $20,000 and Maintenance = $10,000 Prepare a Manufacturing Overhead Budget (When entering agwers in the manufacturing everhead budete the direct laber budget for your cell references) Use 'ROUND Tunction to round the predetermined overhead allocation rate to two decimal places. Manufacturing overhead is allocated using direct labor hours. Brides Manufacturing Overhead Budget Bided it to be produced Nuded VOH Bited FOH Total budgeted POH Budgeted manufacturing overhead costs Director hour(DUN) Predetermined overhead allocation rate per DHE Thunder Creek Company use the first fust out of inventory casting method. The Beginning Finished Goods wem Begin by calculating the projected cost to produce each unitin 2018 based on projected sales. (Hint:in "Cost per unit table cell references come from Direct Materials, Direct Labor and Manufacturing Overhead budgets.) USE OUNOTTO D r od manufacturing overhead cost per unit to two decal places ued mufacturing the best Prepare a cost of Goods Sold Budget. (Hint: Units per month calculated using colorences to both sales budget and production budet) Direct marco per unit Director cost per unit Manufacturing overhead cost per unit Total projected manufacturing cost per unit 2018 Jan Feb Mar 01 Total Budget #6: Cost of Goods Sold Budget Beginning Finished Goods Inventory, 3,600 units. Units produced and sold in 2018 Cost per unit Units per month Total cost of units produced and sold in 2018 Total budgeted 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.) 2019 Budget #7: Selling and Administrative Expense Budget Jan Feb Mar 01 Total Salaries expense Advertising expense Depreciation expense Supplies expense Total budgeted S&A expense Excel Skills 1. Create formulas with cell references. 2. Use the ROUND function. Excel skills 1. Create formulas with cell references. 2. Use the ROUND function. Excel Skills 1. Create formulas with cell references. 2. Use the ROUND function