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 are added to make a finished box ready for sale. The data in the Excel template at the ExPrep site. Remember to carefully plan the layout of the requested schedules to provide a professional, clear and easily understood presentation. 1. Using the workbook from ExPrep, complete the following: a. For worksheet 1: i. Format cells appropriately to show cents, and Direct Labor Hours as a Number field with 1 decimal place. Don't forget appropriate underlining. ii. Use the SUM() function to calculate totals. Next, use the IF function as follows to check that your formulas are correct. In the cell where the expense row and quarterly column totals intersect (the year's total of all the expenses combined), use the IF function to display the correct total if the sum of columns is equal to the sum of rows, or "Add Error" message (spelling matters) 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. At the bottom of each Schedule use the Sum function to create a total. There is a grand total for the 3 schedules at the bottom. This should use and If statement ensures total expenses tie into worksheet 1, and display the total or a "Not same as WS1 total" message if they don't (spelling maters). 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 (i.e., has both Fixed and Variable components) Select one of these for each expense type in the column to the right of the annual amount. iii. In the two columns to the right of your initial judgment (from ii), use the Intercept (to the cent) and Slope (to 2 decimal places - i.e., to the cent per DLH) functions to determine the fixed and variable components of each cost type and category. Ensure cells are properly formatted. iv. In the 3 columns to the right of those for (iii), use IF and AND functions based on (iii) to determine if the type of cost is fixed, variable, or mixed. (NOTE: ignore fixed costs of less than $800 per quarter and variable costs less than $.05 per DLH) Each cell should contain the column label or be blank e.g., in Fixed column, it will read Fixed, or be blank; in Variable column, it will read either Variable or be blank, and in Mixed it will read either Mixed or be blank. Since Mixed costs have both fixed and variable components, for Mixed costs, all three Type columns (Fixed, Variable and Mixed) will be populated. In the next (i.e., a 4th) column, compare the results of these 3 columns to your initial judgment in (ii) and say whether it has changed. Select one of the options in the dropbox. v. Using results of your analysis in (iii) and (iv) create a cost formula per year for each expense in a new column in the following format using cell referencing: This will display the total annual costs, which should tie into col. B unless there are fixed or variables cost components that are less than the thresholds in the NOTE in (iv) above. For fixed costs = Fixed cost per quarter x 4 For variable costs = Variable cost per DLH x DLH per WS1 (cell reference) For mixed costs = Fixed Cost per above + Variable Cost per above e.g., = F6 * 4 + Variable cost per DLH *DLH per WS1 (cell reference) vi. Format your columns and outputs appropriately

Please answer it in Excel and send me pics. Also, provide a full description of you're calculations and formulas.!!!

worksheet 1

worksheet 2

worksheet 2

Qi Annual Total 3 Quarterly Expenses 4 Advertising Stick-ons 5 Apprentice Carpenter Labor 6 Carpenter Labor 7 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 Finished Goods Packaging 17 Parts and Repairs 18 Shipping Costs 19 Utilities 20 Warehouse Rent 21 Wood 22 23 Quarterly Total 24 25 Direct Labor Hours 26 WS1 Data ws2 Analysis Type Direct Materials S 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 4,657 S 26,300 38,290 5,584 92,650 89,405 9,230 22,905 118,500 8,753 3,230 22,208 4,028 4,733 34,358 11.402 20,550 546,525 Q2 5,693 $ 31,945 45,210 6,529 92,650 108,895 9,788 27,995 118,500 10,698 3,230 27,143 4,923 5,484 41,993 15,227 20,550 667,975 03 6,986 S 39,058 55,485 7,610 92,650 133,258 9,915 34,358 118,500 13,129 3,230 33,311 6,041 5,931 51,536 12,566 20,550 819,788 04 8,539 47,493 67,815 8,827 92,650 162,502 10,067 41,993 118,500 16,046 3,230 40,714 7,375 6,398 62,989 21,725 20,550 1,001,963 65210 2973.0 9783.5 11960.5 Lists Annual Cost () () (iv ( (iv) Fixed, Variable or Mixed? Intercept Slope Fixed? Variable? Mixed? Change from (2 Annual Cost Equation Pick one of the above (Fixed cost/artr) Cost per DLH 2. Direct Materials Schedule 2b 4 Description 5 6 Advertising Stick ons 7. Painting/finishing Supplies 8 Hardware 9 Information Stickers 10 Finished Goods Packaging 11 Wood 12 13 Total Direct Materials 14 15 Direct Labor Schedule 17 Description Annual Cost IR Apprentice Carpenter labor 19 Carpenter Labor 20 Master Carpenter Labor 21 22 Total Direct labor 23 24 Manufacturing Overhead Schedule WSi Data WS2 Analysis G H 11 Apprentice Carpenter Labor 19. Carpenter Labor 20 Master Carpenter Labor 21 22. Total Direct Labor 23 24 Manufacturing Overhead Schedule 25 26 Description Annual Cost 27 Cleaning Supplies 28 Depreciation 29 General Supplies 30 Indirect Salaries 31. Insurance 32 Parts and Repairs 32 Shipping costs 34 Utilities 35 Warehouse Rent 36 37. Total Manufacturing Overhead 36 39 40 Grand Total 41 - ON WSI Out WS2 Analysis Units Qi Annual Total 3 Quarterly Expenses 4 Advertising Stick-ons 5 Apprentice Carpenter Labor 6 Carpenter Labor 7 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 Finished Goods Packaging 17 Parts and Repairs 18 Shipping Costs 19 Utilities 20 Warehouse Rent 21 Wood 22 23 Quarterly Total 24 25 Direct Labor Hours 26 WS1 Data ws2 Analysis Type Direct Materials S 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 4,657 S 26,300 38,290 5,584 92,650 89,405 9,230 22,905 118,500 8,753 3,230 22,208 4,028 4,733 34,358 11.402 20,550 546,525 Q2 5,693 $ 31,945 45,210 6,529 92,650 108,895 9,788 27,995 118,500 10,698 3,230 27,143 4,923 5,484 41,993 15,227 20,550 667,975 03 6,986 S 39,058 55,485 7,610 92,650 133,258 9,915 34,358 118,500 13,129 3,230 33,311 6,041 5,931 51,536 12,566 20,550 819,788 04 8,539 47,493 67,815 8,827 92,650 162,502 10,067 41,993 118,500 16,046 3,230 40,714 7,375 6,398 62,989 21,725 20,550 1,001,963 65210 2973.0 9783.5 11960.5 Lists Annual Cost () () (iv ( (iv) Fixed, Variable or Mixed? Intercept Slope Fixed? Variable? Mixed? Change from (2 Annual Cost Equation Pick one of the above (Fixed cost/artr) Cost per DLH 2. Direct Materials Schedule 2b 4 Description 5 6 Advertising Stick ons 7. Painting/finishing Supplies 8 Hardware 9 Information Stickers 10 Finished Goods Packaging 11 Wood 12 13 Total Direct Materials 14 15 Direct Labor Schedule 17 Description Annual Cost IR Apprentice Carpenter labor 19 Carpenter Labor 20 Master Carpenter Labor 21 22 Total Direct labor 23 24 Manufacturing Overhead Schedule WSi Data WS2 Analysis G H 11 Apprentice Carpenter Labor 19. Carpenter Labor 20 Master Carpenter Labor 21 22. Total Direct Labor 23 24 Manufacturing Overhead Schedule 25 26 Description Annual Cost 27 Cleaning Supplies 28 Depreciation 29 General Supplies 30 Indirect Salaries 31. Insurance 32 Parts and Repairs 32 Shipping costs 34 Utilities 35 Warehouse Rent 36 37. Total Manufacturing Overhead 36 39 40 Grand Total 41 - ON WSI Out WS2 Analysis Units