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!! :)
!!NEED BUDGET #5-7 I put the previous budgets so you have the numbers you need!!
I only need Budgets #5-7 please, I gave you the previous 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 $us per unit. Prepare a sales budget 2018 Budget: Sales Budget lan Feb Mar Q1 Total April May Budgeted units to be sold 19.000 25,000 31.000 75.000 35.000 17.000 Sales price per unit 35 15 35 35 Total Sales $ 665,000 $75,000 5 1,055,000 S 2,625,000 5 1,225,000 $ 1,295,000 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 referentes. Enter all values as positive without a minus simni row 18.) Hint: Beginning inventory for the period is equal to the ending inventory of the previous period. Dec 2017 2018 Budget Production Budget lan Teb Mar 1 Total April May 15 Hodgeted units to be sold 19,000 25,000 31.000 75,000 15.000 27,000 16 Plus Desired unitsin ending inventory 3,800 3,000 1,200 7.000 7,000 7.400 17 Total units needed 24,000 31,200 18.000 32.000 42,400 18 Less: Units in beginning inventory 1,500 5.000 0,200 3,100 7.000 19 Budgeted units to be produced 20,200 26,200 31,00 7,200 35,400 20 Thunder Creek Company set pounds of det materials for each unit it produces, at a cost of $5.00 per pound. The company begins the year with 10,00 pounds of 21 material in law Materials inventory Management desires an ending inventory of 23% of next month'y materials requirements 22 Prepare abwe Materials Budget. (When entering answers in the deect materials budget, use the production budget for your cell references. Enter all values as poutive without a minus signin 27 24 2018 Budgetti e Materials Budget lan Mar 01 Total April 26 budgeted units to be produced 20,200 26.300 3.1.000 7,200 35,400 27 Direct materials (pounds) per un 3.00 3.00 3.00 2.00 1. 26 Direct materials needed for production 60.600 7600 95.000 234,600 101,200 9 Plus Desired de materials in ending inventory pounds 19,690 23.0 25,550 26,550 30 Total de materials needed 100,450 121.950 20150 31 Less Direct materials in beginning inventory (pound) 15.150 19,650 23,50 15,150 2 Budgeted purchase of direct materials 65.100 12.100 98,100 246.000 Det material cost per pound 5.00 5.00 Nudgeted cost of direct matt purchases 5 325.500 4100005 10,500 5 1,200,000 5.00 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 Jan Feb Mar Q1 Total Budgeted units to be produced 20,200 26,200 31,800 78,200 Direct labor hours per unit 0.50 0.50 0.50 0.50 Direct labor hours needed for production 10,100 13.100 15.900 39,100 Duct labor cost per hour 22 22 22 22 Budgeted direct labor cost S 222,200 $ 288,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 $2,00 per Indirect labor cost $2.30 per DLR: Maintenance = $2.20 per DUH 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 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 Feb Mar Q1 Total Budgets: Manufacturing Overhead Budget lan Budgeted units to be produced 4VOH cost per unit 15 Budgeted VOH Budgeted row 57 Depreciation so Salaries and maintenance Total budgeted roll 60 budgeted manufacturing overhead costs 61 2 Direct labor hours (O 62 Predetermined overhead allocation rate per ut 04 65 Thunder Creek Company uses the first in first-out (FFO) inwentory.cost method 5 Thunder Creek Company uses the first in, first-out (FIFO) Inventory costing method. 56 The Beginning Finished Goods Inventory is $136,800 consisting of 3,800 units. 7 Begin by calculating the projected cost to produce each unitin 2018 based on projected sales. (Hint: In "Cost per unit table, cell references come from Direct Materials, Dire 58 Use 'ROUND' function to round the fixed manufacturing overhead cost per unit to two decimal places. 59 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 71 Direct material cost per unit 12 Direct labor cost per unit 73 Manufacturing overhead cost per unit 74 Total projected manufacturing cost per unit 25 76 2018 77 Budget 16: Cost of Goods Sold Budget lan Feb Mar 01 Total To Beginning Finished Goods Inventory, 3.800 units. 79 Units produced and sold in 2018 Cost per unit 01 Units per month Total cost of units produced and sold in 2018 63 Total budgeted cost of goods sold 30 34 Thunder Creek Company's variable supple's expense per month is $100 per ont. The feeling and administrative expenses per month consist of Sales 05 $255,000. Advertising 335.000 and Depreciation $38.000 16 Prepare a Selling and Administratie Expense Budget (When enteng as the song and aduse budget use the sales budget for your cell references 017 2018 a Budget : Selling and Administrative Expense Budget lan Feb Mar 01 Total 19 Salaris expense 90 Advertising expense 91 Depreciation expense 92 Supplies expense 93 Total budgeted SEA expense 04 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 $15 per unit. Prepare a sales budget 2013 Budget: Sales Budget lan Feb Mar 01 Total April May Budgeted units to be sold 15,000 25,000 31000 75.000 35.000 17.000 Soles price per unit 15 35 35 35 35 Tots 5 665,000 75.000 1,095,000 2,695.000 $ 1,225,000 $ 1,295,000 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 sip-in row 18.) Hunt teginning inventory for the period is equal to the ending inventory of the previous period 2017 Dec lan 19.000 April 35,000 MAY 37,000 7100 3,000 5.000 4 Budet: Production Budget uted units to be cold 10 Plox Deured unitsin endir inventory 17 Tots needed 18 Units in beginningirwentory 10 Budgeted units to be produced Teti 25,000 5,200 11,200 5.000 26,200 2018 Mar 1 Total 31.000 25.000 7,000 7.000 11.000 12,000 0,200 31.100 21,200 34000 3.800 20,200 1,1600 42.400 7,000 35,400 20 Thunder Creek Company uses 1 pounds of direct materials for each unit it produces at a cost of 55.00 per pound. The company begins the year with 30.00 pounds of 21 material in Raw Materials inventory Management desires an ending inventory of 29% of next month's materials requirements 22 Prepare a Direct Materials Nudget. (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 23 24 2018 2 Budget Oect Materials Budget lan Mar 1 Total April 26 Budgeted units to be produced 20.200 26,200 31.500 78,200 15,400 27 Dect rate as (pounds) per un 3.00 1.00 3.00 3.00 100 Direct materials needed for production 00.000 71.000 95.400 234,600 106.200 20 Plun: Desired direct materials in ending inventory pounds 15,650 21.150 2.550 50 Total de materials needed 0,250 100.450 121.950 261.150 31 Less Direct materials in beginning inventory (pounds 15.150 19,650 21.15 15,150 Budgeted purchase of dired materials 100 32,500 90,100 246.000 Drect material cost per pound 5.00 5.00 5.00 3.00 Muwted cost of direct materials purchases 228,500 414/0005 450,500 5 1,210,000 15 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 Jan Feb Mar Q1 Total Budgeted units to be produced 20,200 26,200 31,800 Direct labor 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 Duet labor cost per hour 39.100 22 22 22 22 Budgeted direct labor cost $ 222, 2005 288,200 $ 349,000 $ 860,200
