Brett White is considering starting a Web-based educational business, e-Prep MBA. He plans to offer a short-course review of accounting for students entering MBA programs. The materials would be available on a password-protected Web site, students would complete the course through self-study. White would have to grade the course assignments, but most of the work would be in developing the course materials, setting up the site, and marketing. Unfortunately, White's hard drive crashed before he finished his financial analysis. However, he did recover the following partial CVP chart (Click the icon to view the partial CVP chart.) Read the requirements e dostu Dollars Wale Rever 5000ring theme Area 40000 30000 20000 Sales Revenue 10000 0 0100000000000000 588 SOSURE 40000 30000 380g Snorre Arse 10000 0 010Q0OOOOOOOOO 50000 5000 ng logo Area 40000 muspst 40000 SH Reven 30000 30000 Bertho Ancorre Area 20000 Total Cost line 10000 10000 0 0 OTZOBOBOBB0006 01020BOGB000000 Requirement 2. If White attracts 200 students to take the course, will the venture be profitable? Explain your answer, If White attracts 200 students to take the course, the be profitable. The graph shows the venture operating if 200 students register for the will be course Tatum Company manufactures drinking glasses. One unita padage of eight glasses, which sells for $24. Tatum projects sales for April I be 5,000 packages, with sales increasing by 150 packages per month for May June July On Tatumbar 225 packages on hand but decides to maintain an ending inventory of 10% of the next month's sales. Prepare a sales budget and a production budget for Tatum for Apr May, and June Sales Budget April, May, and June Apni May June Total 5 000 5,150 5,300 15.450 $ 2413 24 5 24s 24 S 120.000 $ 123,600 S 127.200 $370.800 Budgeted packages to be sold Sates price per package Total sales Now prepare a production budget for ont, May, and June Tatum Company Production Budget Apni, May, and June April May June Total 5000 Budgeted packages to be sold Plus Desired packages in ending inventory Total packages needed Chinose from any list or enter any number in the input fields and then click Check Answer Check AS A D 1 MASTIR 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 Excelt 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. If you copy/paste from the Instructions tab 7 8 9 Requirements Possible Points 10 1. Prepare a Sales Budget. 18 11 2. Prepare a Production Budget. 26 12 39 3. Prepare a Direct Materials Budget. 13 4. Prepare a Direct Labor Budget. 20 14 5. Prepare a Manufacturing Overhead Budget. 33 15 20 6. Prepare a cost of Goods Sold Budget. 20 16 7. Prepare a Selling and Administrative Expense Budget. 17 18 19 Excel Skills 20 1. Create formulas with cell references. 21 2. Use the ROUND function. 6 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.) 2018 Budget #4: Direct Labor Budget Jan Feb Mar Q1 Total Budgeted units to be produced 1 Direct labor hours per unit Direct labor hours needed for production Direct labor cost per hour Budgeted direct labor cost Thunder Creek Company prepares its Manufacturing Overhead Budipet. For each direct labor hour, the variable overhead costs are: Indirect Materials $1.00 per DUH: Indirect Labor Cost $1.30 per DLH; Maintenance $1.20 per DH 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, 2018 Jan Feb Mar 01 Total Budget #S: Manufacturing Overhead Budget Budgeted units to be produced VOH cost per unit Hudgeted VOH Budgeted FOH Depreciation Trunder Creek Company pounds of direct materials for each pro Mac Of Sperond. The other with pounds of material in Baw Materialentory Management des end ventory of month's materiam 23 Pepe De Materials de Winters in the east, the production before her all the 24 NO 25 Materials A 26 ted us to be produced Otec moteris pounds) per 20 material need for production 29 Plus Desired rect materials in ending intory and 30 Total material needed 01 Det materials in beginning entory pod Beted purchase of direct materials 35 Direct material cost per pound 24 Budgeted out of direct materials purchases 35 36 Thunder Creek company workerere minutes of labor to produce each unit of product. The labor costs per hour IT Prepare a cabordet. (Whenever answers in the doctor, we the direct materials for your celebrences) Tel Mar QI Total B Direct Labor Budget Budgeted nuts to be produced 41 Director hours per unit ution ENTERANSWERS 201 PM 16/01 72 Vietherart ADE D H Thunderne Company expects sales of 1,000 units inhuary 2018, 24,000 units in February 30,000 units in March 1.000 in April, and 1.000 in May. The sales price per unit Prepare a sales budget 3 2018 det: Sales Budget tan Feb M 01 Total Budgeted units to be sold April May sales pe perint 7 Total Sales an 9 Thunder Creek wants to finish each month with 20% of next month's sales in units 10 Prepare production budget. (When entering answers in the production budget, use the sales budget for your cell references. Enter all values as positive without a minussen-in row 18.3 Hint: Beginning inventory for the period is equal to the ending inventory of the previous period 12 13 2012 2018 14 Budget 2: Production fudget Dec Feb Mar Q.1 Total April May 15 Budgeted units to be sold 16 Plus: Desired unitsin ending inventory 17 Total units needed 15 Less Units in beginning inventory 19 Budgeted units to be produced 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 son pounds of 21 material in Raw Materials inventory Management desires an ending inventory of 25% of next month's materials requirements Instructions ENTERANSWERS Tina Thunder Creek Company uses the first in, first out (FFO) inventory costing method The Beginning Finished Goods inventory is $86,400 consisting of 2,600 units 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 Use ROUND function to round the fixed manufacturing overhead cost per unit to two decimal places Prepare a cost of Goods Sold Budget. (Hint: Units per month calculated using colleferences to both sales budget and production budget Cost per unit Direct material cost per unit Direct labor cost per unit Manufacturing overhead cost per unit Total projected manufacturing cost per unit 01 Total 2018 7 Budget 16: Cost of Goods Sold Bladget Jan feb MM 3 Beginning Finished Goods Inventory, 3.000 units 9 units produced and sold in 2011 30 Cost per unit 31 Units per month 32 Total cost of units produced and sold in 2015 83 Total budgeted cost of goods sold 84 Thunder Creek Company's variable supplies expense per month is $3.00 per unit. The fixed Sling and administrative penth consist of Sie Insigns ENTERANSWERS 202 = 74 Total projected manufacturing cost per unit 75 76 2018 47 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 unit, produced and sold in 2018 83 Total budgeted cost of goods sold 84 Thunder Creek Company's vanable supplies expense per month is $3.00 per unt. The fixed selling and administrative expenses per month consist of Salaries 85 5245 000. Advertising $30.000. and Depreciation: 528,000 86 Prepare a Selling and Administrative Expense Budget. (When entering answers in the selling and administratw budget, use the sales budget for your cell references.) 87 2018 Jan 88 Budget #7: Selling and Administrative Expense Budget Feb Mar 01 Total 89 salaries expense 90 Advertising expense 91 Depreciation expense 92. Supplies expense 03 Total budgeted S&A expense 94 Brett White is considering starting a Web-based educational business, e-Prep MBA. He plans to offer a short-course review of accounting for students entering MBA programs. The materials would be available on a password-protected Web site, students would complete the course through self-study. White would have to grade the course assignments, but most of the work would be in developing the course materials, setting up the site, and marketing. Unfortunately, White's hard drive crashed before he finished his financial analysis. However, he did recover the following partial CVP chart (Click the icon to view the partial CVP chart.) Read the requirements e dostu Dollars Wale Rever 5000ring theme Area 40000 30000 20000 Sales Revenue 10000 0 0100000000000000 588 SOSURE 40000 30000 380g Snorre Arse 10000 0 010Q0OOOOOOOOO 50000 5000 ng logo Area 40000 muspst 40000 SH Reven 30000 30000 Bertho Ancorre Area 20000 Total Cost line 10000 10000 0 0 OTZOBOBOBB0006 01020BOGB000000 Requirement 2. If White attracts 200 students to take the course, will the venture be profitable? Explain your answer, If White attracts 200 students to take the course, the be profitable. The graph shows the venture operating if 200 students register for the will be course Brett White is considering starting a Web-based educational business, e-Prep MBA. He plans to offer a short-course review of accounting for students entering MBA programs. The materials would be available on a password-protected Web site, students would complete the course through self-study. White would have to grade the course assignments, but most of the work would be in developing the course materials, setting up the site, and marketing. Unfortunately, White's hard drive crashed before he finished his financial analysis. However, he did recover the following partial CVP chart (Click the icon to view the partial CVP chart.) Read the requirements e dostu Dollars Wale Rever 5000ring theme Area 40000 30000 20000 Sales Revenue 10000 0 0100000000000000 588 SOSURE 40000 30000 380g Snorre Arse 10000 0 010Q0OOOOOOOOO 50000 5000 ng logo Area 40000 muspst 40000 SH Reven 30000 30000 Bertho Ancorre Area 20000 Total Cost line 10000 10000 0 0 OTZOBOBOBB0006 01020BOGB000000 Requirement 2. If White attracts 200 students to take the course, will the venture be profitable? Explain your answer, If White attracts 200 students to take the course, the be profitable. The graph shows the venture operating if 200 students register for the will be course Tatum Company manufactures drinking glasses. One unita padage of eight glasses, which sells for $24. Tatum projects sales for April I be 5,000 packages, with sales increasing by 150 packages per month for May June July On Tatumbar 225 packages on hand but decides to maintain an ending inventory of 10% of the next month's sales. Prepare a sales budget and a production budget for Tatum for Apr May, and June Sales Budget April, May, and June Apni May June Total 5 000 5,150 5,300 15.450 $ 2413 24 5 24s 24 S 120.000 $ 123,600 S 127.200 $370.800 Budgeted packages to be sold Sates price per package Total sales Now prepare a production budget for ont, May, and June Tatum Company Production Budget Apni, May, and June April May June Total 5000 Budgeted packages to be sold Plus Desired packages in ending inventory Total packages needed Chinose from any list or enter any number in the input fields and then click Check Answer Check AS A D 1 MASTIR 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 Excelt 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. If you copy/paste from the Instructions tab 7 8 9 Requirements Possible Points 10 1. Prepare a Sales Budget. 18 11 2. Prepare a Production Budget. 26 12 39 3. Prepare a Direct Materials Budget. 13 4. Prepare a Direct Labor Budget. 20 14 5. Prepare a Manufacturing Overhead Budget. 33 15 20 6. Prepare a cost of Goods Sold Budget. 20 16 7. Prepare a Selling and Administrative Expense Budget. 17 18 19 Excel Skills 20 1. Create formulas with cell references. 21 2. Use the ROUND function. 6 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.) 2018 Budget #4: Direct Labor Budget Jan Feb Mar Q1 Total Budgeted units to be produced 1 Direct labor hours per unit Direct labor hours needed for production Direct labor cost per hour Budgeted direct labor cost Thunder Creek Company prepares its Manufacturing Overhead Budipet. For each direct labor hour, the variable overhead costs are: Indirect Materials $1.00 per DUH: Indirect Labor Cost $1.30 per DLH; Maintenance $1.20 per DH 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, 2018 Jan Feb Mar 01 Total Budget #S: Manufacturing Overhead Budget Budgeted units to be produced VOH cost per unit Hudgeted VOH Budgeted FOH Depreciation Trunder Creek Company pounds of direct materials for each pro Mac Of Sperond. The other with pounds of material in Baw Materialentory Management des end ventory of month's materiam 23 Pepe De Materials de Winters in the east, the production before her all the 24 NO 25 Materials A 26 ted us to be produced Otec moteris pounds) per 20 material need for production 29 Plus Desired rect materials in ending intory and 30 Total material needed 01 Det materials in beginning entory pod Beted purchase of direct materials 35 Direct material cost per pound 24 Budgeted out of direct materials purchases 35 36 Thunder Creek company workerere minutes of labor to produce each unit of product. The labor costs per hour IT Prepare a cabordet. (Whenever answers in the doctor, we the direct materials for your celebrences) Tel Mar QI Total B Direct Labor Budget Budgeted nuts to be produced 41 Director hours per unit ution ENTERANSWERS 201 PM 16/01 72 Vietherart ADE D H Thunderne Company expects sales of 1,000 units inhuary 2018, 24,000 units in February 30,000 units in March 1.000 in April, and 1.000 in May. The sales price per unit Prepare a sales budget 3 2018 det: Sales Budget tan Feb M 01 Total Budgeted units to be sold April May sales pe perint 7 Total Sales an 9 Thunder Creek wants to finish each month with 20% of next month's sales in units 10 Prepare production budget. (When entering answers in the production budget, use the sales budget for your cell references. Enter all values as positive without a minussen-in row 18.3 Hint: Beginning inventory for the period is equal to the ending inventory of the previous period 12 13 2012 2018 14 Budget 2: Production fudget Dec Feb Mar Q.1 Total April May 15 Budgeted units to be sold 16 Plus: Desired unitsin ending inventory 17 Total units needed 15 Less Units in beginning inventory 19 Budgeted units to be produced 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 son pounds of 21 material in Raw Materials inventory Management desires an ending inventory of 25% of next month's materials requirements Instructions ENTERANSWERS Tina Thunder Creek Company uses the first in, first out (FFO) inventory costing method The Beginning Finished Goods inventory is $86,400 consisting of 2,600 units 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 Use ROUND function to round the fixed manufacturing overhead cost per unit to two decimal places Prepare a cost of Goods Sold Budget. (Hint: Units per month calculated using colleferences to both sales budget and production budget Cost per unit Direct material cost per unit Direct labor cost per unit Manufacturing overhead cost per unit Total projected manufacturing cost per unit 01 Total 2018 7 Budget 16: Cost of Goods Sold Bladget Jan feb MM 3 Beginning Finished Goods Inventory, 3.000 units 9 units produced and sold in 2011 30 Cost per unit 31 Units per month 32 Total cost of units produced and sold in 2015 83 Total budgeted cost of goods sold 84 Thunder Creek Company's variable supplies expense per month is $3.00 per unit. The fixed Sling and administrative penth consist of Sie Insigns ENTERANSWERS 202 = 74 Total projected manufacturing cost per unit 75 76 2018 47 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 unit, produced and sold in 2018 83 Total budgeted cost of goods sold 84 Thunder Creek Company's vanable supplies expense per month is $3.00 per unt. The fixed selling and administrative expenses per month consist of Salaries 85 5245 000. Advertising $30.000. and Depreciation: 528,000 86 Prepare a Selling and Administrative Expense Budget. (When entering answers in the selling and administratw budget, use the sales budget for your cell references.) 87 2018 Jan 88 Budget #7: Selling and Administrative Expense Budget Feb Mar 01 Total 89 salaries expense 90 Advertising expense 91 Depreciation expense 92. Supplies expense 03 Total budgeted S&A expense 94 Brett White is considering starting a Web-based educational business, e-Prep MBA. He plans to offer a short-course review of accounting for students entering MBA programs. The materials would be available on a password-protected Web site, students would complete the course through self-study. White would have to grade the course assignments, but most of the work would be in developing the course materials, setting up the site, and marketing. Unfortunately, White's hard drive crashed before he finished his financial analysis. However, he did recover the following partial CVP chart (Click the icon to view the partial CVP chart.) Read the requirements e dostu Dollars Wale Rever 5000ring theme Area 40000 30000 20000 Sales Revenue 10000 0 0100000000000000 588 SOSURE 40000 30000 380g Snorre Arse 10000 0 010Q0OOOOOOOOO 50000 5000 ng logo Area 40000 muspst 40000 SH Reven 30000 30000 Bertho Ancorre Area 20000 Total Cost line 10000 10000 0 0 OTZOBOBOBB0006 01020BOGB000000 Requirement 2. If White attracts 200 students to take the course, will the venture be profitable? Explain your answer, If White attracts 200 students to take the course, the be profitable. The graph shows the venture operating if 200 students register for the will be course