MASTER BUDGETS Using Facel to prepare an operating budget manutacturing company Thunder Creek Company is preparing budget for the first quarter of 2018. All relevant information is presented on the Decal template Ar the blur shadedress on the ENTER ANSWERS tab for inputs Awwa u cell references and formulas where appropriate so receive full credit. If you cop pasta from the instructions take you will be marked wrong Enteral amount positive values. Do not miss ar parentheses for any valet 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 Manulturing Overhead Budget 6. Prepare a Cost of Goods Sold Budget 7. Preparea Selling and Administrative Expense ludert Possible Points 18 26 39 20 33 20 20 Excel 1. Create formula with cell reference 2. Uw the DUND function Saving & Submitting solution Soweito desktop Create folder an desktop, and label COMPLETED EXCEL PROJECTS 2 Upload and submit your fileto Degraded Navigate back to the activity window screen where you downloaded the initial spreadsheet Click voor buiten under locate the file you just saved and click on 6. Click Upload button under ep 3 #clea Somit button under 14 Viewing Results Click on bin Mounting 2 Click on the Agent you were working on 3 Click on Project in this will bring up your caree . Within WareCard window, the Comments Report wereld her with B D F H A C E G 1 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. 2 Prepare a sales budget. 3 2018 4 Budget 1: Sales Budget Jan Mar Q.1 Total April 5 Budgeted units to be sold Sales price per unit 7 Total Sales Feb May 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 Mary 15 Budgeted units to be sold 16 Plus: Desired units in ending inventory 17 Total units needed 18 Less Units in beginning inventory 19 Budgeted units to be produced 20 Thunder Creek Company uses 2 pounds of direct materials for each unit it produces a cost of $4.00 per pound. The company begins the year with 9,500 pounds of material in Raw Materials 21 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 2018 25 Budget 23: Direct Materials Budget Jan Feb Mar Q1 Total April 26 Budgeted units to be produced 27 Oirect 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 33 Direct material cost per pound 34 Budgeted cost of direct materials purchases 35 c D G Jan 76 2018 77 Budget 16: Cost of Goods Sold Budget Feb Mar Q1 Total 78 Beginning Finished Goods Inventory. 3,600 units. 79 Units produced and 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 84 Thunder Creek Company's variable supplies expense per month is $3.00 per unit. The fored seling and administrative expenses per month consist of Salaries: $245,000 85 Advertising: $30,000; and Depreciation: $28,000 36 Prepare a Selling and Administrative Expense Budget. (When entering answers in the selling and administrative budget, use the sales budget for your cell references.) 87 2018 88 Budget 17: Selling and Administrative Expense Budget Jan Feb Mar 91 Total 89 Salaries expense 90 Advertising expense 91 Depreciation expense 92 Supplies expense 93 Total budgeted S&A expense 94 95 B D F H A C G 1 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. 2 Prepare a sales budget. 2 2018 4 Budget : Sales Budget Jan Mar Q1 Total April 5 Budgeted units to be sold 6 Sales price per unit 7 Total Sales Feb May 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 2: Production Budget Dec Jan Feb Mar Q1 Total April Mary 15 Budgeted units to be sold 16 Plus: Desired units in ending inventory 17 Total units needed 18 Less: Units in beginning inventory 19 Budgeted units to be produced 20 Thunder Creek Company uses 2 pounds of direct materials for each unit it produces, acost of 54.00 per pound. The company begins the year with 9,500 pounds of material in Raw Materials 21 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 2018 25 Budget 23: Direct Materials Budget Jan Feb Mar 91 Total April 26 Budgeted units to be produced 27 Oirect materials (pounds per unit 28 Direct materials needed for production 29 Plus: Desired direct materials in ending inventory (pounds! 30 Total direct materials needed 11 Less Direct materials in beginning inventory (pounds) 32 Budgeted purchase of direct materials 33 Direct material cost per pound 34 Budgeted cost of direct materials purchases 35 MASTER BUDGETS Using Excel to prepare an operating budget manufacturing company Thunder Creek Corgany is preparing but for a fost quarter of 2018. All rdevant information is presented on the sceltemplate Use the blue shaded grees on the ENTER ANSWERS tab for input Always use cal references and formulas where appropriate to receive full credit. If you cow/paste from the instructions to you will be marked wrong, Enter amounts es positive values. Do not use a minus vign or parentheses for any values Requirements 1. Prepare a Sales Budget 2. Prepare Production Budget 1. Prepwea 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 Adminive Expense lugt Possible Points 18 26 39 20 13 20 20 1. Conform with cell reces. 2. ROUND Saving & Submitting Solution 1 Save fileto de a Crestefolder onderkop, and label COMPLETED EXCEL PROJECTS 2 Uploat and submit your file to be graded Navigate back to the activity window screen where you downloaded the initial spreadsheet Click Choose button under the locate the file you used and did Open Click Upload button under vep d. Click Submit button under stop Viewing Results 1 Click on Hesults tab in My Accountinglab 2 Click on the Agent you were working on 3 Click on Project in this will bring up your Score Card 4 Within Score Card window, click on Live Comments Report flower right to download spreadsheet with feedback D G H 1 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. 2 Prepare a sales budget. 3 2018 4 Budget #1: Sales Budget Jan Feb Mar Q1 Total April May 5 Budgeted units to be sold 6 Sales price per unit 7 Total Sales 8 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 2: Production Budget Dec lan Feb Mar Q1 Total April May 15 Budgeted units to be sold 16 Plus: Desired units in ending inventory 17 Total units needed 18 Less Units in beginning Inventory 19 Budgeted units to be produced 20 H 20 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 21 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 2018 25 Budget 13. Direct Materials Budget Jan Feb Mar Q1 Total April 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 33 Direct material cost per pound 34 Budgeted cost of direct materials purchases 35 36 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 materials budget for your cell references.) 38 2018 39 Budget 14: Direct Labor Budget Jan Feb Mar Q1 Total 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 Budgeted direct labor cost 45 H A D 45 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 DUH: Maintenance = $1.20 per DLM 47 The Fixed Overhead Costs per month are: Salaries of $40,000 Depreciation $20,000 and Maintenance $10,000 48 Prepare a Manufacturing Overhead Budget. (When entering answers in the manufacturing overhead budget, use the direct labor budget for your cell references 49 Use 'ROUND' function to round the predetermined overhead alocation rate to two decimal places. Manufacturing overhead is located using direct labor hours, 50 51 2018 52 Budget #5: Manufacturing Overhead Budget Jan Feb Mar Q1 Total 53 Budgeted units to be produced 54 VOH cost per unit 55 Budgeted VOH 56 Budgeted FOH 57 Depreciation 58 Salaries and maintenance 59 Total budgeted FOH 60 Budgeted manufacturing overhead costs 62 Direct labor hours (DLHY) 63 Predetermined overhead alocation rate per DLC 64 65 Thunder Creek Company uses the first in, first-out (FIFO) Inventory costing method. 66 The Beginning Finished Goods Inventory is $86,400 consisting of 3,600 units 67 Begin by calculating the projected cost to produce each unt in 2018 based on projected sales. (Hint: In "Cost per un table, cell references come from Direct Materials, Direct labor, and Manufacturing Overhead budgets.) 68 Use 'ROUND'function to round the feed manufacturing overhead cost per unit to two 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 H D F G 69 Prepare a cost of Goods Sold Budget. (Hint: Units per month calculated using cell references to both sales budget and production budget.) 70 Cost per unit 71 Direct material cost per unit 72 Direct labor cost per unit 73 Manufacturing overhead cost per unit 74 Total projected manufacturing cost per unit 75 76 2018 77 Budget #6: Cost of Goods Sold Budget Jan Feb Mar Q1 Total 78 Beginning Finished Goods Inventory, 3,600 units. 79 Units produced and 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 84 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 85 Advertising: $30,000; and Depreciation: $28.000 86 Prepare a Selling and Administrative Expense Budget. (When entering answers in the selling and administrative budget, use the sales budget for your cell references.) 87 2018 88 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 OR