You are analyzing production costs at a furniture-making plant, where wood and hardware is cut, painted and finished by carpenters into individual pieces. Each piece is then packaged in one or more boxes and information and advertising stickers added to make a finished box ready for sale. Remember to carefully plan the layout of the requested schedules to provide a professional, clear and easily understood presentation 1. Watch the overview video, 2 Excel skill videos, and read the 2 articles on Excel functions. 2. Using the workbook from Exprep, complete the following: a. For worksheet 1: i. Format cells appropriately to show cents, and Direct Labor Hours to 1 decimal place. ii. Use the SUMO and IFO functions to calculate totals and check that your formulas are correct. (Use IF fn to ensure sum of columns is equal to sum of rows, with Add Error" as message if they don't). b. For worksheet 2 you will be performing an analysis of the data in 3 Schedules. i. Insert a formula to capture the appropriate amount for each expense type. Ensure the cell is properly formatted. There is a grand total for the 3 schedules at the bottom. This should ensure total expenses tie in to worksheet 1, and display the total or a "Not same as input totals" message if they don't ii. Review the quarterly cost behavior in worksheet 1 and use your judgement to estimate whether each expense is (note there are no wrong answers for this); 1. Fixed 2. Variable (based on direct labor hours), or 3. Mixed (ie has both Fixed and Variable components) Select one of the three above for each expense type in the column to the right of the annual amount 11. In the two columns to the right of your initial judgement (from 11), use the Intercept (to the cent) and Slope (to 2 decimal places - which is to the cent per DLH) functions to determine the fixed and variable components of each cost type and category iv. In the 3 columns to the right of those for (111), use IF and AND functions based on (1) to determine if type of cost is fixed variable or mixed (NOTE ignore fixed costs of less than $500 per quarter and variable costs less than 01 In the next (ie, a 4 column, compare the results of these 3 columns to your initial judgement in (11) and say whether it has changed to do so, select one of the options in the drop box W. Using results of your analysis in (111) create a cost formula per year for each expense in a nev column in the following format using cell referencing For mixed costs Fixed Cost - Variable Cost per DLH x DLH Delivery Expense 5200,00 - 45 DLH For variable costs - Variable cost per DLH DLH For fixed costs Fixed cost per year Quarterly Expenses 4 Advertising Stick-ons 5 Apprentice Carpenter Labor & Carpenter Labor 2 Cleaning Supplies 8 Depreciation 9 Painting/finishing Supplies 10 General Supplies 11 Hardware 12 Indirect Salaries 13 Information Stickers 14 Insurance 15 Master Carpenter Labor 16 Fished Goods Packaging 17 Parts and Repairs 18 Shipping Costs 19 utilities 20 V/chouse Rent 2 wood Type Direct Materials Direct Labor Direct Labor Man Overhead Man Overhead Direct Materials Man Overhead Direct Materials Man Overhead Man Overhead Man Overhead Direct Labor Man Overhead Direct Materials Man Overhead Man Overhead Man Overhead Direct Materials Q1 5012.5 23062.5 35332.5 5141.25 91205 87052.5 9125 21562.5 118500 8486.25 4115 21528.75 3721.25 4491.25 33678.75 11290 19575 547262.5 Q2 6497.5 28187.5 42817.5 6013.75 91225 106097.5 9110 25207.5 119500 10378.75 Q3 7628.75 34543.75 52548.75 7004.38 91250 130678.75 9115 31388.75 118000 12739.38 4115 31843.13 5571,88 5626.38 50368.13 12451 19575 815193.75 04 8946.25 42131.25 64225.75 8113.13 91225 153896.25 10115 38806.25 119000 15568.13 4115 38919.38 6796.63 5985.13 61494,38 21610 19575 996281.25 Annual Total 28085 127925 194924.5 26272.51 364905 477725 37465 116965 475000 47172,51 16460 118237,51 20633.51 20272.51 186637.51 60463 78300 3026725 4115 25946.25 4543.75 4169.75 41096.25 15112 19575 667987.5 Quarterly Total 1050142.5 1227580.5 1439641.78 1706803.78 88 Dat Labor Hours 6544 7996 9806.5 11965.5 36312 WST Data W52 Analysis Excel Asst 3F20 Direct Materials Schedule 2h Description Annual Cost Feed Varor Mixed Pick one of the above (iv) (W) iv) Intercept Slope Feed? Variable? Mixed! Change from Cost Equation Fed cost/orto) Cost per DLH Artising Socken Painting Mining Supplies Hartwie Infomation Stickers Fished Goods Packaging Wood Total De Labo Schedule Annual Cost Description Apprentice Carpenter liber Cor Labor Master Carpenter Labor Total Manufacturing Ovwed Schedule Denti Clean uppies Annual Cost full You are analyzing production costs at a furniture-making plant, where wood and hardware is cut, painted and finished by carpenters into individual pieces. Each piece is then packaged in one or more boxes and information and advertising stickers added to make a finished box ready for sale. Remember to carefully plan the layout of the requested schedules to provide a professional, clear and easily understood presentation 1. Watch the overview video, 2 Excel skill videos, and read the 2 articles on Excel functions. 2. Using the workbook from Exprep, complete the following: a. For worksheet 1: i. Format cells appropriately to show cents, and Direct Labor Hours to 1 decimal place. ii. Use the SUMO and IFO functions to calculate totals and check that your formulas are correct. (Use IF fn to ensure sum of columns is equal to sum of rows, with Add Error" as message if they don't). b. For worksheet 2 you will be performing an analysis of the data in 3 Schedules. i. Insert a formula to capture the appropriate amount for each expense type. Ensure the cell is properly formatted. There is a grand total for the 3 schedules at the bottom. This should ensure total expenses tie in to worksheet 1, and display the total or a "Not same as input totals" message if they don't ii. Review the quarterly cost behavior in worksheet 1 and use your judgement to estimate whether each expense is (note there are no wrong answers for this); 1. Fixed 2. Variable (based on direct labor hours), or 3. Mixed (ie has both Fixed and Variable components) Select one of the three above for each expense type in the column to the right of the annual amount 11. In the two columns to the right of your initial judgement (from 11), use the Intercept (to the cent) and Slope (to 2 decimal places - which is to the cent per DLH) functions to determine the fixed and variable components of each cost type and category iv. In the 3 columns to the right of those for (111), use IF and AND functions based on (1) to determine if type of cost is fixed variable or mixed (NOTE ignore fixed costs of less than $500 per quarter and variable costs less than 01 In the next (ie, a 4 column, compare the results of these 3 columns to your initial judgement in (11) and say whether it has changed to do so, select one of the options in the drop box W. Using results of your analysis in (111) create a cost formula per year for each expense in a nev column in the following format using cell referencing For mixed costs Fixed Cost - Variable Cost per DLH x DLH Delivery Expense 5200,00 - 45 DLH For variable costs - Variable cost per DLH DLH For fixed costs Fixed cost per year Quarterly Expenses 4 Advertising Stick-ons 5 Apprentice Carpenter Labor & Carpenter Labor 2 Cleaning Supplies 8 Depreciation 9 Painting/finishing Supplies 10 General Supplies 11 Hardware 12 Indirect Salaries 13 Information Stickers 14 Insurance 15 Master Carpenter Labor 16 Fished Goods Packaging 17 Parts and Repairs 18 Shipping Costs 19 utilities 20 V/chouse Rent 2 wood Type Direct Materials Direct Labor Direct Labor Man Overhead Man Overhead Direct Materials Man Overhead Direct Materials Man Overhead Man Overhead Man Overhead Direct Labor Man Overhead Direct Materials Man Overhead Man Overhead Man Overhead Direct Materials Q1 5012.5 23062.5 35332.5 5141.25 91205 87052.5 9125 21562.5 118500 8486.25 4115 21528.75 3721.25 4491.25 33678.75 11290 19575 547262.5 Q2 6497.5 28187.5 42817.5 6013.75 91225 106097.5 9110 25207.5 119500 10378.75 Q3 7628.75 34543.75 52548.75 7004.38 91250 130678.75 9115 31388.75 118000 12739.38 4115 31843.13 5571,88 5626.38 50368.13 12451 19575 815193.75 04 8946.25 42131.25 64225.75 8113.13 91225 153896.25 10115 38806.25 119000 15568.13 4115 38919.38 6796.63 5985.13 61494,38 21610 19575 996281.25 Annual Total 28085 127925 194924.5 26272.51 364905 477725 37465 116965 475000 47172,51 16460 118237,51 20633.51 20272.51 186637.51 60463 78300 3026725 4115 25946.25 4543.75 4169.75 41096.25 15112 19575 667987.5 Quarterly Total 1050142.5 1227580.5 1439641.78 1706803.78 88 Dat Labor Hours 6544 7996 9806.5 11965.5 36312 WST Data W52 Analysis Excel Asst 3F20 Direct Materials Schedule 2h Description Annual Cost Feed Varor Mixed Pick one of the above (iv) (W) iv) Intercept Slope Feed? Variable? Mixed! Change from Cost Equation Fed cost/orto) Cost per DLH Artising Socken Painting Mining Supplies Hartwie Infomation Stickers Fished Goods Packaging Wood Total De Labo Schedule Annual Cost Description Apprentice Carpenter liber Cor Labor Master Carpenter Labor Total Manufacturing Ovwed Schedule Denti Clean uppies Annual Cost full