H 1 1 M N 0 P RE s T U V BC D E G 1 MASTE 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. 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. 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 values 7 8 9 Requirements Possible Points 10 1 Prepare a Sales Budget 18 11 2. Prepare a Production Budget. 26 12 3. Prepare a Direct Materials Budget 39 13 4. Prepare a Direct Labor Budget 20 14 5. Prepare a Manufacturing Overhead Budget 33 15 6. Prepare a cost of Goods Sold Budget. 20 16 7. Prepare a Selling and Administrative Expense Budget 20 17 18 19 Excel Skills 20 1 Create formulas with cell references 21 2 Use the ROUND function 22 23 24 Saving & Submitting Solution 25 1 Save fle to desktop 26 2. Create folder on desktop, and label COMPLETED EXCEL PROJECTS 27 2 Upload and submit your file to be graded 28 2. Navigate back to the activity window screen where you downloaded the initial spreadsheet 29 b. Gick Choose button under step 3; locate the file you just saved and dick Open 30 c Click Upload button under step 3 31 d. Click Submit button under step 4 32 33 34 Viewing Results 35 1 Click on Results tab in My Accountinglab 35 2 Click on the Assignment you were working on 37 3 Click on Project link, this will bring up your Score Card 38 4 Within Score Card window, click on Live Comments Report lower right) to download spreadsheet with feedback 39 40 41 B D H E 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 18,000 24,000 30,000 72,000 34,000 36.000 6 Sales price per unit 34 34 34 34 34 34 7 Total Sales $ 612,000$ 816,000 $ 1,020,000 $ 2,448,000 $ 1,156,000 $ 1,224,000 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 Jan Feb Mar Q1 Total April May 15 Budgeted units to be sold 18,000 24,000 30,000 72.000 34,000 36,000 16 Plus: Desired units in ending inventory 3,600 4,800 6,000 6,800 17,600 7,200 17 Total units needed 22,800 30,000 36,800 89,600 41,200 18 Less: Units in beginning inventory 3,600 4,800 6,000 14,400 6,800 19 Budgeted units to be produced 19,200 25,200 30,800 75,200 34,400 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 #3: Direct Materials Budget Feb Mar Qi Total April 26 Budgeted units to be produced 19,200 25,200 30,800 75,200 34,400 27 Direct materials (pounds) per unit 2.00 2.00 2.00 2.00 2.00 28 Direct materials needed for production 38,400 50,400 61,600 150,400 68,800 29 Plus: Desired direct materials in ending inventory (pounds) 12,600 15,400 37,600 17,200 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 4.00 4.00 4.00 4.00 34 Budgeted cost of direct materials purchases 35 Jan B E H 2018 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 39 Budget #4: Direct Labor Budget Jan Feb Mar Q1 Total 40 Budgeted units to be produced 19,200 25,200 30,800 75,200 41 Direct labor hours per unit 0.50 0.50 0.50 0.50 42 Direct labor hours needed for production 9,600 12,600 15,400 37,600 43 Direct labor cost per hour 20 20 20 20 44 Budgeted direct labor cost $ 192,000 $ 252,000 $ 308,000 $ 752,000 45 Thunder Creek Company prepares its Manufacturing Overhead Budget. For each direct labor hour, the variable overhead costs are: 46 Indirect Materials = $1.00 per DLH; Indirect Labor Cost = $1.30 per DLH; Maintenance = $1.20 per DLH 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 allocation rate to two decimal places. Manufacturing overhead is allocated using direct labor hours. 50 51 52 Budget #5: Manufacturing Overhead Budget Jan Feb 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 61 62 Direct labor hours (DLHT) 63 Predetermined overhead allocation rate per DLHO 2018 Mar c D H O TIEU Venca acara per en 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 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.) 68 Use "=ROUND" function to round the fixed 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.) 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: 85 $245,000; 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 96 97 98 99