D41 1 MASTER BUDGETS 2 Using Excel to prepare an operating budget (manufacturing company) 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 shaded areas on the ENTER-ANSWERS tab for inputs. 6 ALWAYS use cell references and formulas where appropriate to receive full credit. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values. 9 Requirements 10 11 2. Prepare a Production Budget. 12 13 4. Prepare a Direct Labor Budget. 14 5. Prepare a Manufacturing Overhead Budget. 15 16 7. Prepare a Selling and Administrative Expense Budget. Possible Points 1. Prepare a Sales Budget. 18 26 39 20 3. Prepare a Direct Materials Budget. 6. Prepare a Cost of Goods Sold Budget. 20 20 18 19 Excel Skills 20 21 1. 2. Create formulas with cell references. Use the ROUND function. 23 24 Saving & Submitting Solution 25 26 27 28 29 30 31 32 1 Save file to desktop. Create folder on desktop, and label COMPLETED EXCEL PROJECTS b. a. Save your solution in the folder you just created; add -solution-date to end of file name 2 Upload and submit your file to begraded a. b. c. d. Navigate back to the activity window-screen where you downloaded the initial spreadsheet Click Choose button under step 3; locate the file you just saved and click Open Click Upload button under step 3 Click Submit button under step 4 2018 Mar Budget #3: Direct Materials Budget Budgeted units to be produced Direct materials (pounds) per unit Direct materials needed for production Plus: Desired direct materials in ending inventory (pounds) Total direct materials needed Less: Direct materials in beginning inventory (pounds) Budgeted purchase of direct materials Direct material cost per pound Budgeted cost of direct materials purchases Jan Feb Q1 Total April 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 answers in thedirect labor budget, use the direct materials budget for your cell references.) 2018 Budget #4: Direct Labor Budget Budgeted units to be produced Direct labor hours per unit Direct labor hours needed for production Direct labor cost per hour Budgeted direct labor cost Jan Feb Mar Q1 Total 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 The Fixed Overhead Costs per month are: Salaries of $40,000, Depreciation $20,000 and Maintenance $10,000 Prepare a Manufacturing Overhead Budget. When entering answers in the manufacturing overhead budget, use the direct labor budget for your cell references.) Use ROUND'function to round the predetermined overhead allocation rate to two decimal places. Manufacturing overhead is allocated using direct labor hours. 2018 Budget #S: Manufacturing Overhead Budget Budgeted units to be produced VOH cost per unit Budgeted VOH Budgeted FOH Jan Feb Mar Q1 Total Depreciation Salaries and maintenance Total budgeted FOH Budgeted manufacturing overhead costs Direct labor hours (DLHr) Predetermined overhead allocation rate per DLHr 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. n 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, Direct Labor, and Manufacturing Overhead budgets.) Use -ROUND'uncion to round the fixed manufacturing overhead ost 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.) Cost per unit Direct material cost per unit Direct labor cost per unit Manufacturing overhead cost per unit Total projected manufacturing cost per unit 2018 Budget #6: Cost ofGoods Sold Budget Beginning Finished Goods Inventory, 3,600 units. Units produced and sold in 2018 Jan Feb Mar Q1 Total Cost per unit Units per month Total cost of units produced and sold in 2018 Total budgeted cost of goods sold 9 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 Seling and Administrative Expense Budget. hen entering answers n the seling and ad is at budget use he sales budget or ca references. 2018 Budget #7: Selling and Administrative Expense Budget Salaries expense Advertising expense Jan Feb Mar Q1 Total 1Depreciation expense Supplies expense Total budgeted S&A expense