D41 fx A B C E F G H J K L M N 0 P 1 MASTER BUDGETS 2 Using Excel to prepare an operating budget (manufacturing company) 3 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. 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 1 Save file to desktop. a. Create folder on desktop, and label COMPLETED EXCEL PROJECTS b. Save your solution in the folder you just created; add-solution-date to end of file name 2 Upload and submit your file to be graded. 25 26 27 28 29 30 31 c. Click Upload button under step 3 32 d. Click Submit button under step 4 33 a. Navigate back to the activity window-screen where you downloaded the initial spreadsheet b. Click Choose button under step 3; locate the file you just saved and click Open 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. Budget #1: Sales Budget Budgeted units to be sold Sales price per unit 2018 Jan Feb Mar Q1 Total April May 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. Budget #2: Production Budget 2017 2018 Dec Jan Feb Mar Q1 Total April May 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 Raw Materials Inventory. Management desires an ending inventory of 25% of next month's materials requirements 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 35.) 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 2018 Jan Feb Mar 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 the direct labor budget, use the direct materials budget for your cell references.) Budget #4: Direct Labor Budget Budgeted units to be produced Direct labor hours per unit Direct labor hours needed for production Jan 2018 Feb Mar Q1 Total Direct labor cost per hour Budgeted direct labor cost 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. 4 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; 5 Advertising: $30,000; and Depreciation: $28,000 6 Prepare a Selling and Administrative Expense Budget. (When entering answers in the selling and administrative budget, use the sales budget for your cell references.) 7 Budget #7: Selling and Administrative Expense Budget Jan 2018 Feb Mar Q1 Total Salaries expense Advertising expense 1 Depreciation expense 2 Supplies expense 3 Total budgeted S&A expense 4 5