Master Budgets
Using excel to prepare an operating budget
(manufacturing company)
Thunder Creek Company is preparing budgets for
the forst quarter of 2018. All relevant information is
presented on the excel template.
use the blue shaded areas on the ENTER-
ANSWERS tab for inputs. Use cell references and
formulas please!! :) Please us cell refemces so i can understand how you got your numbers :)
I only need budgets #5-7 please i put the numbers you need above :) and please list cell references so I can understand the numbers :)
Thunder Creek Company expects sales of 19,000 units in January 2018, 25.000 units in February 31,000 units in March, 35,000 in April, and 37,000 in May. The sales price is SIS per unit. Prepare a sales budget. 2018 Budget 1: Sales Budget lan Feb Mar 01 Total April May Budgeted units to be sold 15,000 25,000 31.000 15.000 35.000 17.000 Sales price per unit 15 15 35 5 5 Total 5 665,000 35,000 1,085,000 2,695.000 S 1,225,000 $ 1,295,000 3 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 18.) Hint: Beginning inventory for the period is equal to the ending inventory of the previous period. 2017 Dec May lan 19.000 37,000 1,100 3.000 Budget Production Budget 15 budgeted units to be cold 10 Plus Deured unitsin ending inventory 17 Tots needed 18 w Units in beginningirwentory 13 Budgeted units to be produced Teb 25,000 5,200 11,200 5.000 26,200 2018 Mar 1 Total 1.000 75,000 7.000 7,000 000 12,000 0.100 2,100 11,100 21,200 April 35.000 7400 42,400 7,000 35,400 24,000 3800 20.00 20 Thunder Creek Company uses pounds of direct materials for each unit it produces at a cost of 55.00 per pound. The company begins the year with 10.500 pounds of 21 material in Raw Materials inventory Management desires an ending inventory of 2 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 minussi 23 24 2018 25 Budget Oct Materials de lan Feb Mar 1 Total April 26 budgeted units to be produced 20,200 26,200 31,00 78.200 35,400 27 Direct materials (pounds) per unit 3.00 3.00 3.00 3.00 100 3: Direct materials needed for production 782000 95.00 234,600 10.200 29 plun: Desired direct materials in ending inventory rounds 15,650 23 26,550 36,550 30 Total de materials needed 10,290 102,450 121.950 26.150 31 tessut materials in beginning inventory (pounds) 15,150 19,650 27.50 15,150 32 Budgeted purchase of dired materials 6.100 32,500 93,100 246,000 21 Dured material cost per pound 5.00 5.00 5.00 Meted cost of direct mutels purchases 123,500 400 5 450,500 5 1,210,000 5.00 Thunder Creek Company's workers require 30 minutes of labor to produce each unit of product. The labor cost is $22 per hour Prepare a Direct Labor Budget. (When entering answers in the direct labor budget, use the direct materials budget for your cell references.) 2018 Budget : Direct Labor Budget lan Feb Mar Q1 Total Budgeted units to be produced 20,200 26,200 31. X Direct labot hours per unit 78,200 0,50 0.50 0.50 0.50 Direct labor hours needed for production 10.100 13.100 15.900 39,100 Direct labor cost per hour 22 22 22 22 Budgeted direct labor cost $ 222,200 S 282,200 $ 349,800 $ 860,200 1. Thunder Creek Company prepares its Manufacturing Overhead Budget for each direct labor hour, the variable overhead costs are: Indirect Materials - 52.00 per DLH. Indirect Labor Cost = $2,30 per DLH: Maintenance $2.20 per DL.H 2. The Fixed Overhead Costs per month are: Salaries of $50,000, Depreciation $30,000 and Maintenance $20,000 Prepare a Manufacturing Overhead Budget (When entering answers in the manufacturing overhead budget, use the direct laber budget for your cell references Use ROUND function to round the prodetermined overhead allocation rate to two decimal places, Manufacturing overhead is allocated using direct labor hours. 2018 Jan Feb Mar QI Total Rudget 5: Manufacturing Overhead Rudget Hadgeted units to be produced VOH cost per unit Badpeted VOH Balgeted POH Depreciation Salaries and maintenance Total budgeted FOH Bulgeted manufacturing overhead coats Direct labor hours (DLH) Predeterminel overhead allocation rate per DHE Thunder Creek Company uses the first-in, first-out (FIFO) inventory costing method. The Beginning Finished Goods Inventory is $136,800 consisting of 3.800 units Begin by calculating the projected cost to produce cach unit in 2018 bored on projected sales. (Hint: In "Cost per unit table cell references come from Direct Materials, Direct Labor and Manufacturing Overhead budget Use ROUNDY function to round the feed manufacturing overhead cost per unit to two decimal places Prepare a Cost of Goods Sold Badget. (Hint: Unit per month calculated using cell references to both sales budget and production budget.) Caut per unit Direct material cost per unit Director contri Manufacturing overhead cost per unit Total projected manufacturing cout per unit 201N Rodge Cast of Gands Sold Budget Jan Web Mar QI Total Beginning Finished Goods Inventory. 3,800 units Units produced and sold in 2015 Couper unit Units per month Total cost of units produced and sold in 2018 Total budgeted cost of goods sold Thunder Creek Company's variable supples expense per month is 54.00 per unit. The fixed seling and administrative expenses per month consist of Satarios $255.000 Advertising $35.000, and Depreciation: 538,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) Jan Feb Mar OI Total Budget 17. Selling and Administrative Expense Budget Salaries experte Advertising experie Depreciation pense Supplies expense Total bud & expone Thunder Creek Company expects sales of 19,000 units in January 2018, 25.000 units in February 31,000 units in March, 35,000 in April, and 37,000 in May. The sales price is SIS per unit. Prepare a sales budget. 2018 Budget 1: Sales Budget lan Feb Mar 01 Total April May Budgeted units to be sold 15,000 25,000 31.000 15.000 35.000 17.000 Sales price per unit 15 15 35 5 5 Total 5 665,000 35,000 1,085,000 2,695.000 S 1,225,000 $ 1,295,000 3 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 18.) Hint: Beginning inventory for the period is equal to the ending inventory of the previous period. 2017 Dec May lan 19.000 37,000 1,100 3.000 Budget Production Budget 15 budgeted units to be cold 10 Plus Deured unitsin ending inventory 17 Tots needed 18 w Units in beginningirwentory 13 Budgeted units to be produced Teb 25,000 5,200 11,200 5.000 26,200 2018 Mar 1 Total 1.000 75,000 7.000 7,000 000 12,000 0.100 2,100 11,100 21,200 April 35.000 7400 42,400 7,000 35,400 24,000 3800 20.00 20 Thunder Creek Company uses pounds of direct materials for each unit it produces at a cost of 55.00 per pound. The company begins the year with 10.500 pounds of 21 material in Raw Materials inventory Management desires an ending inventory of 2 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 minussi 23 24 2018 25 Budget Oct Materials de lan Feb Mar 1 Total April 26 budgeted units to be produced 20,200 26,200 31,00 78.200 35,400 27 Direct materials (pounds) per unit 3.00 3.00 3.00 3.00 100 3: Direct materials needed for production 782000 95.00 234,600 10.200 29 plun: Desired direct materials in ending inventory rounds 15,650 23 26,550 36,550 30 Total de materials needed 10,290 102,450 121.950 26.150 31 tessut materials in beginning inventory (pounds) 15,150 19,650 27.50 15,150 32 Budgeted purchase of dired materials 6.100 32,500 93,100 246,000 21 Dured material cost per pound 5.00 5.00 5.00 Meted cost of direct mutels purchases 123,500 400 5 450,500 5 1,210,000 5.00 Thunder Creek Company's workers require 30 minutes of labor to produce each unit of product. The labor cost is $22 per hour Prepare a Direct Labor Budget. (When entering answers in the direct labor budget, use the direct materials budget for your cell references.) 2018 Budget : Direct Labor Budget lan Feb Mar Q1 Total Budgeted units to be produced 20,200 26,200 31. X Direct labot hours per unit 78,200 0,50 0.50 0.50 0.50 Direct labor hours needed for production 10.100 13.100 15.900 39,100 Direct labor cost per hour 22 22 22 22 Budgeted direct labor cost $ 222,200 S 282,200 $ 349,800 $ 860,200 1. Thunder Creek Company prepares its Manufacturing Overhead Budget for each direct labor hour, the variable overhead costs are: Indirect Materials - 52.00 per DLH. Indirect Labor Cost = $2,30 per DLH: Maintenance $2.20 per DL.H 2. The Fixed Overhead Costs per month are: Salaries of $50,000, Depreciation $30,000 and Maintenance $20,000 Prepare a Manufacturing Overhead Budget (When entering answers in the manufacturing overhead budget, use the direct laber budget for your cell references Use ROUND function to round the prodetermined overhead allocation rate to two decimal places, Manufacturing overhead is allocated using direct labor hours. 2018 Jan Feb Mar QI Total Rudget 5: Manufacturing Overhead Rudget Hadgeted units to be produced VOH cost per unit Badpeted VOH Balgeted POH Depreciation Salaries and maintenance Total budgeted FOH Bulgeted manufacturing overhead coats Direct labor hours (DLH) Predeterminel overhead allocation rate per DHE Thunder Creek Company uses the first-in, first-out (FIFO) inventory costing method. The Beginning Finished Goods Inventory is $136,800 consisting of 3.800 units Begin by calculating the projected cost to produce cach unit in 2018 bored on projected sales. (Hint: In "Cost per unit table cell references come from Direct Materials, Direct Labor and Manufacturing Overhead budget Use ROUNDY function to round the feed manufacturing overhead cost per unit to two decimal places Prepare a Cost of Goods Sold Badget. (Hint: Unit per month calculated using cell references to both sales budget and production budget.) Caut per unit Direct material cost per unit Director contri Manufacturing overhead cost per unit Total projected manufacturing cout per unit 201N Rodge Cast of Gands Sold Budget Jan Web Mar QI Total Beginning Finished Goods Inventory. 3,800 units Units produced and sold in 2015 Couper unit Units per month Total cost of units produced and sold in 2018 Total budgeted cost of goods sold Thunder Creek Company's variable supples expense per month is 54.00 per unit. The fixed seling and administrative expenses per month consist of Satarios $255.000 Advertising $35.000, and Depreciation: 538,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) Jan Feb Mar OI Total Budget 17. Selling and Administrative Expense Budget Salaries experte Advertising experie Depreciation pense Supplies expense Total bud & expone
