please help me solve this problem I cant figure it out
ng cebeld st o re abcde De AaBbCD Acede AaBb No Spacing Headings Heading 2 Winter 2020 ACC 381 - Accounting Information Systems Lab Excel Assignment 3 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. (Total 291 pts) 1. Complete watching the assigned videos: Excel is Fun Basics Video 26, and https://www.youtube.com/watch?v=VTKLHvchL7M re Formulas, Slope, and Intercept fns 2. Using the daria provided on the following page complete the following in Excel: a. For worksheet 1: i. Use the data worksheet provided on Canvas but save using filename per #3 below. Format to show cents, and Direct Labor Hours to 2 decimal places. ii. Use the SUMO and IFO functions to check your data entry. (Use IF fn to ensure sum of columns is equal to sum of rows, with "Add Error" as message if they don't). (37 pts) ill. Format the columns using appropriate formats. (35 pts) b. For worksheet 2 you will be performing an analysis of the data. i. Create a schedule for each of the following expense categories by putting each expense and its annual amount under one of the following headings: 1. Direct Materials 2. Direct Labor 3. Manufacturing Overhead -- costs associated with operation of the plant and its equipment that are not directly associated with finished goods shipped out of the plant Each schedule should be in alphabetical order and have a total, and there should be a grand total to ensure total expensestie in to worksheet 1, and not equal" message if they don't. (54 pts) II. Review the quarterly cost behavior in worksheet 1 and use your judgement to estimate whether each expense is: 1. Fixed 2. Variable (based on direct labor hours), or 3. Mixed (ie.. has both Fixed and Variable components) Document this in the column to the right of the annual amount (18 pts) TWIU IUI LALII UI the following expense categories by putting each expense and its annual amount under one of the following headings: 1. Direct Materials 2. Direct Labor 3. Manufacturing Overhead - costs associated with operation of the plant and its equipment that are not directly associated with finished goods shipped out of the plant Each schedule should be in alphabetical order and have a total, and there should be a grand total to ensure total expenses tie in to worksheet 1, and not equal" message if they don't. (54 pts) ii. Review the quarterly cost behavior in worksheet 1 and use your judgement to estimate whether each expense is: 1. Fixed 2. Variable (based on direct labor hours), or 3. Mixed (i.e., has both Fixed and Variable components) Document this in the column to the right of the annual amount. (18 pts) iii. In the two columns to the right of your initial judgement (from ii), use the Slope (to 2 decimal places - which is to the cent per DLH) and Intercept (to the cent) functions to determine the fixed and variable components of each cost type and category. (44 pts) iv. In one (or three) columns to the right of those for (iii), use IF and AND functions based on (iii) to determine if type of cost is fixed, variable, or mixed. Do the results of (iii) change your classification of any of the expenses from (ii) (NOTE: ignore fixed costs of less than $500 per quarter and variable costs less than OL? (54 pts) If so, Note the revised category in the column to the right of the column(s) for (iv). (10 pts) V. Using results of your analysis in (iii) create a cost function per year for each expense in a new column. It must display the formula (not a number) in the following format: (18 pts) Cost = Fixed Cost + Variable Cost per DLH eg. Delivery Expense = $200.00 + 45 DLH vi. Format your columns and outputs appropriately. (21 pts) 3. Upload your worksheet using the following naming convention: UNIQUENAMEExcel3.XLSX on Canvas. QL Advertising Stickers Apprentice Carpenter Labor Carpenter Labor Cleaning supplies Depreciation Painting/finishing Supplies General Supplies Hardware Indirect Salaries Information Stickers Insurance Master Carpenter Labor Finished Goods Packaging Equipment Parts and Repairs Shipping Costs Utilities Warehouse Rent Wood Total Expenses $ 4,352.50 22,462.50 35,332.50 5.141.25 91,275.00 88,052.50 9,115.00 21,552.50 118,500.00 8,426.25 3.115.00 21.528.75 3.701.25 4.291.25 33,378.75 11.287.00 19.275.00 543,262.50 $ 1.044,050 Quarterly Costs Q2 Q3 Q4 Annual Total S 5,097.50 $ 6,028.75 $ 7.146.25 $ 22,625 27,387.50 33,543.75 40,931.25 124,325 42,817.50 52,548.75 64,225.75 194.925 6,013.75 7,004.38 8,113.13 26,273 91,275.00 91.275.00 91.275.00 365,100 107,397.50 131,578.75 154.596.25 481.625 9.115.00 9.115.00 10.115.00 37,460 25,197.50 31.378.75 38.796.25 116,925 118,500,00 118.500.00 118.500.00 474,000 10.298,75 12.639.38 15,448.13 46,813 3.115.00 3.115,00 13.115.00 12.460 25.946.25 31.843.13 38.919:38 118.238 4.523.75 5.551.88 6.776.63 20,554 4.969.75 5.326,38 5.685.13 20.273 40.796.25 50.068.13 61,194,38 185,438 15.112.00 12.451.00 21.610.00 60.460 19.275.00 19.275.00 19.275.00 77.100 663.987.50 814.893.75995.981.25 3.018.125 S 1.220.826 S1.436,137 S1.701.704 S5,402.716 Quarterly DL Hours Direct Labor Hours 7,996 Total DLH 36.312 2.807 11.966 ng cebeld st o re abcde De AaBbCD Acede AaBb No Spacing Headings Heading 2 Winter 2020 ACC 381 - Accounting Information Systems Lab Excel Assignment 3 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. (Total 291 pts) 1. Complete watching the assigned videos: Excel is Fun Basics Video 26, and https://www.youtube.com/watch?v=VTKLHvchL7M re Formulas, Slope, and Intercept fns 2. Using the daria provided on the following page complete the following in Excel: a. For worksheet 1: i. Use the data worksheet provided on Canvas but save using filename per #3 below. Format to show cents, and Direct Labor Hours to 2 decimal places. ii. Use the SUMO and IFO functions to check your data entry. (Use IF fn to ensure sum of columns is equal to sum of rows, with "Add Error" as message if they don't). (37 pts) ill. Format the columns using appropriate formats. (35 pts) b. For worksheet 2 you will be performing an analysis of the data. i. Create a schedule for each of the following expense categories by putting each expense and its annual amount under one of the following headings: 1. Direct Materials 2. Direct Labor 3. Manufacturing Overhead -- costs associated with operation of the plant and its equipment that are not directly associated with finished goods shipped out of the plant Each schedule should be in alphabetical order and have a total, and there should be a grand total to ensure total expensestie in to worksheet 1, and not equal" message if they don't. (54 pts) II. Review the quarterly cost behavior in worksheet 1 and use your judgement to estimate whether each expense is: 1. Fixed 2. Variable (based on direct labor hours), or 3. Mixed (ie.. has both Fixed and Variable components) Document this in the column to the right of the annual amount (18 pts) TWIU IUI LALII UI the following expense categories by putting each expense and its annual amount under one of the following headings: 1. Direct Materials 2. Direct Labor 3. Manufacturing Overhead - costs associated with operation of the plant and its equipment that are not directly associated with finished goods shipped out of the plant Each schedule should be in alphabetical order and have a total, and there should be a grand total to ensure total expenses tie in to worksheet 1, and not equal" message if they don't. (54 pts) ii. Review the quarterly cost behavior in worksheet 1 and use your judgement to estimate whether each expense is: 1. Fixed 2. Variable (based on direct labor hours), or 3. Mixed (i.e., has both Fixed and Variable components) Document this in the column to the right of the annual amount. (18 pts) iii. In the two columns to the right of your initial judgement (from ii), use the Slope (to 2 decimal places - which is to the cent per DLH) and Intercept (to the cent) functions to determine the fixed and variable components of each cost type and category. (44 pts) iv. In one (or three) columns to the right of those for (iii), use IF and AND functions based on (iii) to determine if type of cost is fixed, variable, or mixed. Do the results of (iii) change your classification of any of the expenses from (ii) (NOTE: ignore fixed costs of less than $500 per quarter and variable costs less than OL? (54 pts) If so, Note the revised category in the column to the right of the column(s) for (iv). (10 pts) V. Using results of your analysis in (iii) create a cost function per year for each expense in a new column. It must display the formula (not a number) in the following format: (18 pts) Cost = Fixed Cost + Variable Cost per DLH eg. Delivery Expense = $200.00 + 45 DLH vi. Format your columns and outputs appropriately. (21 pts) 3. Upload your worksheet using the following naming convention: UNIQUENAMEExcel3.XLSX on Canvas. QL Advertising Stickers Apprentice Carpenter Labor Carpenter Labor Cleaning supplies Depreciation Painting/finishing Supplies General Supplies Hardware Indirect Salaries Information Stickers Insurance Master Carpenter Labor Finished Goods Packaging Equipment Parts and Repairs Shipping Costs Utilities Warehouse Rent Wood Total Expenses $ 4,352.50 22,462.50 35,332.50 5.141.25 91,275.00 88,052.50 9,115.00 21,552.50 118,500.00 8,426.25 3.115.00 21.528.75 3.701.25 4.291.25 33,378.75 11.287.00 19.275.00 543,262.50 $ 1.044,050 Quarterly Costs Q2 Q3 Q4 Annual Total S 5,097.50 $ 6,028.75 $ 7.146.25 $ 22,625 27,387.50 33,543.75 40,931.25 124,325 42,817.50 52,548.75 64,225.75 194.925 6,013.75 7,004.38 8,113.13 26,273 91,275.00 91.275.00 91.275.00 365,100 107,397.50 131,578.75 154.596.25 481.625 9.115.00 9.115.00 10.115.00 37,460 25,197.50 31.378.75 38.796.25 116,925 118,500,00 118.500.00 118.500.00 474,000 10.298,75 12.639.38 15,448.13 46,813 3.115.00 3.115,00 13.115.00 12.460 25.946.25 31.843.13 38.919:38 118.238 4.523.75 5.551.88 6.776.63 20,554 4.969.75 5.326,38 5.685.13 20.273 40.796.25 50.068.13 61,194,38 185,438 15.112.00 12.451.00 21.610.00 60.460 19.275.00 19.275.00 19.275.00 77.100 663.987.50 814.893.75995.981.25 3.018.125 S 1.220.826 S1.436,137 S1.701.704 S5,402.716 Quarterly DL Hours Direct Labor Hours 7,996 Total DLH 36.312 2.807 11.966
