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!! :)

MASTER BUDGETS Using Excel to prepare an operating budget (manufacturing company) Thunder Creek Company is peeparing budgets for the first quarter of 2018. All relevant information is presented on the Excel template Die de sex on the INTER ANSWERS tahun Always well ferences and formules where appropriate to rethe full eredit. If you copy paste from the Instruction to you will be marked wrong inter alia perskil vahes, the nut we ml Requirements I. Prepare a Sales Budget 2. Prepare a Production Budget 3. Prepare a Direct Materials Budget 4. Prepare a Direct Labor Budget Thunder Creck 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 535 per unit Prepare a sales budget 2018 Budget 1: Sales Budget Feb Mar QI Total April May Hudgeted units to be sold Sales prieperit Toual Sales Thunder Creek wants to finish cach month with 20% of next month's sales in units. Prepare a production budget. (When entering swers in the production budget, use the sales budget for your cell references. Finter all values as positive-without a minas sign-in row 18) Hint: Beginning inventory for the period is equal to the ending inventory of the previous period. 2017 Jan 2013 Budget #2: Production Badget Dec Feb Mar QI Total April MAY Badgeted units to be sold Plus Desired unitsin ending inventory Total units meded Less: Units in beginning inventory Budgeted units to be produced Thunder Creek Company uses 3 pounds of direct materials for each unit it produces, at a cost of 5.00 per pound. The company begins the year with 10.500 pounds of material in Raw Materiale 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 celf references. Inter all values as positive without a insign-in row) I Total April 2018 Budget #3: Direct Material Budget Jan Feb Mar Budgeted units to be produced Direct materials pounda) per unit Direct materiale meded for protection Plow Desired direct materials in die nentory (pounds) Total direct material needed Les Direct materials in beginning inventory (pound) Hledged porshaw of direct materials Direct mail cost per pound Halvled cont of direct materials purchases Thunder Creek Company's workers pure 30 minutes of labor to produce vach unit of product. The labor cont i 8.2 per hour Prepare a Direct Labor Budget When entering answers in the direct labor bulget, use the direct materials budget for your cell references) 2018 Budget 4: Direct Labor Budget Feb Mar Badgeted units to be produced Direct labor hours per unit Director hour needed for production Direct labor contro Badget direct labor con QI Total Thunder Creck Company prepares its Manufacturing Overhead Hudget. For each direct labor hour, the variable overhead costs are: Indirect Materials - $2.00 per DLH Indirect Labor Cost-$2.30 per DLH: Mainenance 52.20 per DLH 2. The Fixed Overhead Costs per month are. Saluries of $50,000. Depreciation - 530,000 and Maintenance = $20,000, Prepare a Manufacturing Overhead Budget. (When entering answers in the manufacturing overhead budget, use the direct labur budget for your cell references) UHROUND function to round the predetermined overhead allocation Tute to two decimal places Manufacturing overhead is allocated in direct labor hours, | hom Rasipal Sat Tourier chat an a 1 A teetulya 1. 11 triparts hatianies aiyst DA thaniyaal H ) tralia talgal urain Thuircuice pr paral. Te many larges uri 1 D cian h, Tra L ly Del asian thurai TM lalar War alp 4 Det Drop Ithurirena tan * 0 hea us aial inimea) - DUA talgia Mantarargements thadigal Semes faul | Raliyaal art ana hadriranaathane Tags is there is a pear ches, amarnt that is, Later, amal Marrier uratitles 2 UND rise and the tearl amatues aralaal pian lalars Sat talpet ( arai | Titial Ver Plan target M laal amalar Tala alaral ulaila m thaa thra Add.100 Tregama e i trips ( that chain) Sript in tellsg and Miriam a talyst | t | | TA MASTER BUDGETS Using Excel to prepare an operating budget (manufacturing company) Thunder Creek Company is peeparing budgets for the first quarter of 2018. All relevant information is presented on the Excel template Die de sex on the INTER ANSWERS tahun Always well ferences and formules where appropriate to rethe full eredit. If you copy paste from the Instruction to you will be marked wrong inter alia perskil vahes, the nut we ml Requirements I. Prepare a Sales Budget 2. Prepare a Production Budget 3. Prepare a Direct Materials Budget 4. Prepare a Direct Labor Budget Thunder Creck 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 535 per unit Prepare a sales budget 2018 Budget 1: Sales Budget Feb Mar QI Total April May Hudgeted units to be sold Sales prieperit Toual Sales Thunder Creek wants to finish cach month with 20% of next month's sales in units. Prepare a production budget. (When entering swers in the production budget, use the sales budget for your cell references. Finter all values as positive-without a minas sign-in row 18) Hint: Beginning inventory for the period is equal to the ending inventory of the previous period. 2017 Jan 2013 Budget #2: Production Badget Dec Feb Mar QI Total April MAY Badgeted units to be sold Plus Desired unitsin ending inventory Total units meded Less: Units in beginning inventory Budgeted units to be produced Thunder Creek Company uses 3 pounds of direct materials for each unit it produces, at a cost of 5.00 per pound. The company begins the year with 10.500 pounds of material in Raw Materiale 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 celf references. Inter all values as positive without a insign-in row) I Total April 2018 Budget #3: Direct Material Budget Jan Feb Mar Budgeted units to be produced Direct materials pounda) per unit Direct materiale meded for protection Plow Desired direct materials in die nentory (pounds) Total direct material needed Les Direct materials in beginning inventory (pound) Hledged porshaw of direct materials Direct mail cost per pound Halvled cont of direct materials purchases Thunder Creek Company's workers pure 30 minutes of labor to produce vach unit of product. The labor cont i 8.2 per hour Prepare a Direct Labor Budget When entering answers in the direct labor bulget, use the direct materials budget for your cell references) 2018 Budget 4: Direct Labor Budget Feb Mar Badgeted units to be produced Direct labor hours per unit Director hour needed for production Direct labor contro Badget direct labor con QI Total Thunder Creck Company prepares its Manufacturing Overhead Hudget. For each direct labor hour, the variable overhead costs are: Indirect Materials - $2.00 per DLH Indirect Labor Cost-$2.30 per DLH: Mainenance 52.20 per DLH 2. The Fixed Overhead Costs per month are. Saluries of $50,000. Depreciation - 530,000 and Maintenance = $20,000, Prepare a Manufacturing Overhead Budget. (When entering answers in the manufacturing overhead budget, use the direct labur budget for your cell references) UHROUND function to round the predetermined overhead allocation Tute to two decimal places Manufacturing overhead is allocated in direct labor hours, | hom Rasipal Sat Tourier chat an a 1 A teetulya 1. 11 triparts hatianies aiyst DA thaniyaal H ) tralia talgal urain Thuircuice pr paral. Te many larges uri 1 D cian h, Tra L ly Del asian thurai TM lalar War alp 4 Det Drop Ithurirena tan * 0 hea us aial inimea) - DUA talgia Mantarargements thadigal Semes faul | Raliyaal art ana hadriranaathane Tags is there is a pear ches, amarnt that is, Later, amal Marrier uratitles 2 UND rise and the tearl amatues aralaal pian lalars Sat talpet ( arai | Titial Ver Plan target M laal amalar Tala alaral ulaila m thaa thra Add.100 Tregama e i trips ( that chain) Sript in tellsg and Miriam a talyst | t | | TA