I need help with my Excel assignment who can help me subject is Accounting

i need it to be solved for me I havent started yet

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 Interceptofns 2. Using the data 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 in to ensure sum of columns is equal to sum of rows, with "Add Error" as message if they don't). (37 pts) iii. Format the columns using appropriate formats. (35 pts) b. For worksheet 2 you will be performing an analysis of the data. 1. 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 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 cach expense is: 1. Fixed Aanbod Abede AaBbCD AalbCode AaBb Andet iii. 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 cach 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.1 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 01.? (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 e.g. 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. 34 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 QL. $ 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 S 1,044.050 Quarterly Costs 02 03 $ 5,097.50 $ 6,028.75 $ 7.146.25 27,387.50 33,543.75 40,931.25 42,817.50 52,548.75 64,225.75 6,013.75 7.004.38 8,113.13 91,275.00 91,275,00 91,275.00 107.397.50 131,578.75 154,596.25 9,115.00 9,115.00 10,115,00 25,197.50 31,378.75 38.796.25 118,500.00 118,500.00 118.500.00 10,298.75 12.639.38 15,448.13 3.115.00 3.115.00 3,115.00 25,946.25 31.843.13 38.919.38 4.523.75 5.551.88 6,776.63 4.969.75 5.326,38 5.685.13 40.796.25 50.068.13 61,194,38 15.112.00 12.451.00 21.610.00 19.275.00 19.275.00 19.275.00 663,987.50 814.893.75 995.981.25 S 1.220.826 S 1.436.137 $1.701.704 Annual Total $ 22,625 124 325 194.925 26,273 365,100 481,625 37,460 116,925 474,000 46,813 12.460 118.238 20.554 20.273 185.438 60.460 77.100 3.018,125 S5.402.716 Quarterly DL Hours 7.996 9,807 Direct Labor Hours Total DLH 3 6,312 11,966 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 Interceptofns 2. Using the data 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 in to ensure sum of columns is equal to sum of rows, with "Add Error" as message if they don't). (37 pts) iii. Format the columns using appropriate formats. (35 pts) b. For worksheet 2 you will be performing an analysis of the data. 1. 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 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 cach expense is: 1. Fixed Aanbod Abede AaBbCD AalbCode AaBb Andet iii. 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 cach 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.1 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 01.? (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 e.g. 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. 34 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 QL. $ 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 S 1,044.050 Quarterly Costs 02 03 $ 5,097.50 $ 6,028.75 $ 7.146.25 27,387.50 33,543.75 40,931.25 42,817.50 52,548.75 64,225.75 6,013.75 7.004.38 8,113.13 91,275.00 91,275,00 91,275.00 107.397.50 131,578.75 154,596.25 9,115.00 9,115.00 10,115,00 25,197.50 31,378.75 38.796.25 118,500.00 118,500.00 118.500.00 10,298.75 12.639.38 15,448.13 3.115.00 3.115.00 3,115.00 25,946.25 31.843.13 38.919.38 4.523.75 5.551.88 6,776.63 4.969.75 5.326,38 5.685.13 40.796.25 50.068.13 61,194,38 15.112.00 12.451.00 21.610.00 19.275.00 19.275.00 19.275.00 663,987.50 814.893.75 995.981.25 S 1.220.826 S 1.436.137 $1.701.704 Annual Total $ 22,625 124 325 194.925 26,273 365,100 481,625 37,460 116,925 474,000 46,813 12.460 118.238 20.554 20.273 185.438 60.460 77.100 3.018,125 S5.402.716 Quarterly DL Hours 7.996 9,807 Direct Labor Hours Total DLH 3 6,312 11,966