Please provide excel formulas for these two sheets

Scenario You work for a company that produces two different types of Workbench: the WB500 which is the simpler, cheaper option; and the WB660 which has more features but is a bit more expensive. You are required to create a spreadsheet that can conduct a weighted average Cost Volume Profit Analysis for the two products. You can assume that the sales mix, sales price, fixed costs, and variable costs per unit are constant. You will need to calculate the fixed costs, labour costs and material costs and then work out the weighted average break-even point. You have also been asked to show a forecast for different unit volumes and different sales mixes and produce a break-even analysis line chart for the selected volumes and sales mix. Start by downloading the assessment workbook from ilearn and copying/moving it to an appropriate folder. You will need to work on a desktop version of Excel, this can be Mac or PC, but you cannot use Excel online, Google Sheets or Numbers. It is recommended that you use 2016 or later. When you open the file it is very important that you Enable Macros and Content. You will then be asked to enter your Student Number (you will not be able to edit it afterwards, so type it in carefully) and then enter your Student Name. Please note the first 3 worksheets are locked and you will only be able to change the cells specified. General Guidelines . All the light grey cells require you to insert a calculation or a cell reference, i.e. start with an "=", just typing in an answer will result in 0 marks. Do not change the structure of the workbook in anyway or put any workings anywhere other than directed. (Do not add columns/rows, pivots, additional calculations.) Only use rounding functions where specifically requested (although you can format to show/hide additional decimals). The use of named ranges is encouraged, and in certain cases required but unless stipulated calculations do not need to use named ranges. Please do NOT create tables other than where directed. Because you are developing a model where values may be subject to change it is very important that all calculations return a correct answer regardless of the values in the spreadsheet and must still work correctly when the tables of data (BOM & Inventory) are sorted in a different order.B C D E G H 3 WB500 Sales Mix 60%% Tax Rates 4 WB660 Sales Mix 40%% State Tax 107. 5 Unit Increments 1000 Federal Tax 6 7 Sales Volume in Units 1000 2,000 3,000 4,000 5,000 6,000 8 Total $ Sales Value $384,000,00 $768,000.00 $1,152,000.00 $1,536,000,00| $1,920,000.00| $2,304,000.00 9 Variable Costs Direct Material $0.00 12 Direct Labor 13 Other Vary-with-Unit Costs 14 Sales Commissions 15 Total Variable Costs $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 16 17 $ Profit Before Tax [Contribution Margin - Fixed Costs] 18 19 Income Tax 20 State Tax 21 Federal Tax 22 Total Income Tax $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 23 24 $ Profit after Tax 25 26 7. Profit (of Sales] 28 Break-Even Analysis Line Chart Data 29 Fixed Costs 30 Total Costs 31 Total $ Sales Value 32 33 34 .. Labour Costs HR Fixed Costs BOM CVP Analysis Profit Volume Forecast Inventorym U Product Information WB500 WB660 Fixed Costs $179,590.20 LO Unit Sales Price $310.00 $495.00 Units Manufactured and Sold 5100 3366 Vary-with-Revenue Costs Sales Mix (% of total units sold) 60% 40% Sales Commissions 8.5% CO Weighted Average Contribution Margin $3.96 10 Vary with Unit Costs WB500 WB660 Break Even Units (weighted) 45363 Direct Material $98.79 $137.25 12 Direct Labor $35.00 $60.00 Break Even Variable Costs 13 Other Vary-with-Unit Costs $16.38 $19.42 Direct Material $2,236,369.30 14 Total Vary with Unit Costs $150.17 $216.67 Direct Labor $825,444.43 Other Vary-with-Unit Costs $361,084.98 16 Break Even Analysis WB500 WB660 Sales Commissions 3.5% 17 Contribution Margin Per Unit $9.66 $61.66 Total Variable Costs $3,422,898.75 19 Break Even Point in Units/Product 18591.12 2912.59 Break Even Contribution Margin $3,782,079.08 20 Break Even in Sales Dollars $5,763,246.58 $1,441, 731.25 Break Even Profit Before Tax $3,602,488.88 + NM nventory PART CI IMAGE DESCRIPTION SUPPLIER SUPPLIERCOST EACHMIN STOCK IN STOCKBACKORDER CATEGORYRE-ORDER? RE-ORDER QTY Supplier Min Order Qty Parts Average Value ems on Backord PX723541 Arm 320 White & Portico W529196/78 $1.9 200 241 How 25 PX723542 Arm 35 Buggins B557761/77 $2.39 200 198 White & Portico 30 PX374561 IMADE Axle White & Portico | W664956/39 $3.11 100 63 Y Buggins 65 PX820261 Block 12/A White & Portico | W771072/53 $13.64 262 PX820262 Block Front Buggins B567350/21 $ 11.54 100 173 Reorder % over 25% Minimum PX100468 Block Rear 14-X 3 White & Portico | W701735/28 $10.99 100 224 PX820266 Block Rear Buggins B326854/81 $12.09 100 150 PX723593 Bolt How H448251/40 $1.83 200 164 PX374089 Bracket 552 White & Portico W426061/24 $42.49 50 80 PX723557 Foot Buggins B472237/18 $3.24 100 277 PX723820 Foot Sq Buggins B420251/20 $2.39 200 256 PX807530 Grip White & Portico W564399/34 $2.61 200 PX820728 Knob White & Portico W591861/33 $2.39 200 PX723594 Large Bolt Haw H326781/31 $2.19 200 270 Activate Wil Labour Costs HR Fixed Costs BOM CVP Analysis Profit Volume Forecast InventorySection E The following are to be completed in the Profit Volume Forecast Worksheet 19 In this sheet we want to be able to forecast what our profits will look like with different sales volumes and different sales mixes. Click on the drop down in B3 and change it to 50%, the value in B4 adjusts accordingly. Now change it back to 60%. Click in B5 and you will see you can also select the increment (the amount we go up in) we want to model, for example we may want to see profit for 500, 1000, 1500 units or we may want to see profit for 1000, 2000, 3000 units. E1 Click in B7 and you will see you can select the minimum sales volume you want to model. In C7 create a formula to add the Increment in B5 to the volume in B7. Use appropriate cell referencing so that the formula can be dragged across to G7 and the total will increase by the value shown in B5 (and change if the value in 85 changes). Drag the formula across to G7. E2 In row 8 calculate the total sales revenue for each of the different Sales Volumes using the sales mix options selected in B3 and B4 (do not use the sales mix from the CVP sheet.) E3 Complete the variable costs table for the different sales volumes (B11:G14) using the sales mix options selected in B3 and B4. E4 In row 17 calculate Profit Before Tax ( Contribution Margin - Fixed Costs) for each sales volume (this will return a negative value where the unit volume is less than the break-even). ES In B20 calculate the state tax incurred for each sales volume using the rate at the top w of the sheet. If the profit was zero or below, the calculation should return 0. (Do NOT just type 0 as the volumes can change.) Apply appropriate cell referencing so that the formula can be dragged down and then across to complete the tax table. E6 In row 24 calculate the profit after factoring in the income tax (this may return a negative value where the unit volume is less than or close to the break-even). E7 In row 26 work out the profit for each sales volume as a percentage of sales. N L E8 Complete the Break-Even Analysis Line Chart Data in B29 to G31 for each of the Unit volumes. (Note Total Costs includes Variable Costs, Fixed Costs and Income Tax) E9 Use the data in A29:G31 to create a 2D Line Chart that plots the Total Fixed Costs, Total Costs & Total $ Sales. Add the Sales Volume in Units figures as Horizontal Axis Labels and add the Chart Title: Profit Volume Forecast (mark given only if this exact title is used). Use chart tools (not drag and drop) to position the legend at the top of the chart. The completed chart should look something like this (although values will vary). The point where the lines cross is the break even point.Question Instruction Marks Done Section F The following are to be completed in the Inventory Worksheet 15 F1 The inventory shows a list of parts kept in stock, who supplies them and how many 2 are currently in stock. In P4 calculate how many parts are supplied by the supplier shown in N4. Copy the formula down to P6. F2 In Q4 calculate the average cost of parts supplied by the supplier shown in N4. Copy 2 the formula down to Q6. F3 Some parts have been re-ordered but not yet delivered, these are said to be on 2 backorder and are indicated with a Y in the backorder column. In R4 calculate how many parts on backorder are supplied by the supplier shown in N4. Copy the formula down to R6. F4 We have decided to classify parts into two categories. Parts that are less than $5 will be category A and all other products will be Category B. Create a formula in the Category column to calculate the correct category for each part. F5 If a stock item falls below the minimum stock level shown in column G and is NOT on backorder, then we need to reorder it. In K4 create a calculation that will return "Y" if the item needs reordering and leave the cell blank if it does not. F6 Challenge Question (limited help will be given) When we re-order we like to ensure we will have 25% more than the minimum stock level. Create a calculation that checks if we need to reorder, if we do, calculate the number we need to re-order to have an in stock value of 25% over the min stock level, return 0 if re-order not required. The suppliers also impose a minimum order quantity (see column O). Modify your calculation so that if we need to re-order and the re-order quantity is less than the Min Order Qty for the supplier, it returns the appropriate Min Order Qty. (2 Marks will be given for doing just the first part correctly). TOTAL MARKS urther Clarification: The BOM is like a recipe, it itemises which components and how many we need to make one of each type of product