i attched the question down
ITST 1101 Final Spreadsheet Workbook Lab Step 1 - Go to Blackboard and Open “Final_Workbook_Data” Note: As you go, put borders around all data. Data needs to be in the proper format, example currency for costs. All data must be visible and legible. Points will be lost for: no borders, wrong format, illegible and misspellings. Step 2 a. In Column F, create a column titled “Labor Cost”. Using a formula, compute the Labor Cost for each product b. In Column G, create a column titled “Unit Cost: Using a formula that includes Materials, Labor and Shipping Costs. c. In Column H, create a column titled “Unit Labor Cost %”. Using a formula, compute the percentage of labor cost for each unit. 2 decimal places. d. Using a formula, compute the Totals for Columns B-G [excluding H.] Fill cell H17, with a dark green shade. e. Center the data in columns C + E, and format column E data with a comma separator. f. In Column I, create a column titled “Unit % of Sales”. Using a formula, compute the % of each unit again the total sales. 4 decimal places. g. In Column J, create a column titled “Overhead per Unit”. Using a formula, compute the monthly overhead costs for each sold unit. h. Using a formula, compute the totals for I17 and J17. i. In the Title in A1, Insert your initials and center across the sheet. j. Center all the titles, use a light grey fill on the data in rows 2 + 3, merge and center row 1. Step 3 a. Apply Conditional Formatting so that in Labor Costs, >250 is dark red text and light red fill and <250 is="" green="" fill="" with="" dark="" green="" text.="" b.="" apply="" conditional="" formatting="" so="" that="" in="" unit="" labor="" cost,=""> 70% is Bold Italic purple text. c. Create a Line Chart with markers, showing the Unit Labor Cost. Chart Title = Unit Labor Cost %, black and bold text. Make sure all data is legible. d. Re-name Sheet 1 – Products Step 4 a. Go to Sheet 2 and Rename the tab, Employees b. In Column D, create a column titled “Annual Salary”. Wrap the text on the title. Using a formula, based off of the national standard for full-time workers per year, according to the U.S. Office of Personnel Management. c. In Column E, create a column titled “Annual Health Ins Cost”. Wrap the text on the title. Using a formula, compute the annual cost of Health Insurance for each employee. d. In Column F, create a column titled “Annual Workers Comp Cost”. Wrap the text on the title. Using a formula, compute the annual cost of Worker’s Comp Insurance for each employee. e. In Column G, create a column titled “Annual Unemp. Ins. Cost”. Wrap the text on the title. Using a formula, compute the annual cost of Unemployment Insurance for each employee. f. In Column HI, create a column titled “Total Emp. Cost”. Using a formula, compute the annual costs for each employee. [Salary and all Insurance] g. Using a Formula, compute all Total Columns. Merge and Center Acme Industries and Admin Employees across the data. h. Sort the data based upon Employee Name, Ascending. Make sure the data stays aligned correctly to the employee. i. Create a 3D Pie Chart that shows the Total Employee Cost by %. Name the Chart, “Your_Initials Employee Cost.” (Use your own initials) Move the Legend to the right of the chart. Step 5 a. Title Sheet 3 - 2020. b. Copy the entire Employees sheet and paste it into the 2019 sheet. c. Change the Health Insurance rate to 31%, format stays as number. d. Change the Workers Comp rate to 9%, format stays as number. e. Change the Unemployment rate to 19%, format stays as number. f. In Column I, create a column titled “Cost Increase”. Wrap the text on the title. Using a formula, compute the Cost Increase of the Employees. [Total Emp Cost from 2020, minus the Total Emp Cost from the Employees sheet.] Step 6 a. Go to Sheet 4 and rename it “Food Dist” b. Create a Pivot Table, making a new worksheet that shows the Products in rows, with the Customer Information in columns. c. For your Values, you only want the 2 Quarter [Qtr 2] d. Insert a Slicer to your Pivot Table so that only the Customer Names, A-F are shown. e. Rename your sheet with the Pivot table with your Initials [2-3 Characters] Step 7 Save your workbook and upload your .xls or .xlsx file to Blackboard by the deadline. Be sure to upload the workbook to correct place to receive credit. No credit will be given for files uploaded to the wrong location. 1Final_Spreadsheet_Lab Directions Rev 3a Sheet1 Acme Industries ProductsMaterialLabor ShippingAnnualLabor Unit Unit laborUnit %Overhead CostsHoursCost/UnitUnits/SoldCostCostcost %of Sale Per Unit Boom Machine$ 1,550.5035$ 25.0047$1,855.00$ 3,430.50 Anvils$ 960.0022$ 95.0060$1,166.00$ 2,221.00 Cap Gamma Rays$ 360.7515$ 10.00300$795.00$ 1,165.75 Traffic Lights$ 750.455$ 65.00700$ 265.00$ 1,080.45 Jet Motor$ 233.1310$ 45.00300$ 530.00$ 808.13 Roller Skates$ 25.002$ 5.005000$ 106.00$ 136.00 Dehydrated Boulders$ 46.104$ 2.00150$ 212.00$ 260.10 Rockets$ 811.2515$ 75.00324$ 795.00$ 1,681.25 Out-Board Motor$ 345.0012$ 35.00600$ 636.00$ 1,016.00 Detonator$ 15.003.5$ 1.20350$ 185.50$ 201.70 Explosive Tennis Ball$ 13.001$ 0.757500$ 53.00$ 66.75 Disintegrating Pistol$ 78.154.3$ 4.30188$ 227.90$ 310.35 Jet-Propelled Unicycle$ 346.1023$ 75.00157$ 1,219.00$ 1,640.10 Giant Rubber Band$ 1.500.75$ 0.559000$ 39.75$ 41.80 TNT$ 455.0083$ 375.00555$ 4,399.00$ 5,229.00 Totals$ 5,990.93235.55$ 813.8025231$ 12,484.15$ 19,288.88 Overhead Cost/month4400 Labor Rate/hour53 Sheet2 Acme Industries Admin. Employees Years of ServiceHourly RateAnnual Salary costAnnual Health Ins Cost Annual workers Comp CostAnnual Unemp. Ins CostTotal Emp. Cost Babs Bunny25.538 Wile E. Coyote3535 Road Runner327.5 Bugs Bunny2023 Daffy Duck2020 Foghorn Leghorn1818 Yakko510 Wakko510 Dot812 Pinky3025 Brain1020 Slappy Squirrel2525 Totals Insurance Rates% Health 0.27 Workers Comp0.08 Unemployment 0.12 Sheet3 Acme Industries Admin. Employees Years of ServiceHourly RateAnnual Salary costAnnual Health Ins Cost Annual workers Comp CostAnnual Unemp. Ins CostTotal Emp. Cost Babs Bunny25.538 Wile E. Coyote3535 Road Runner327.5 Bugs Bunny2023 Daffy Duck2020 Foghorn Leghorn1818 Yakko510 Wakko510 Dot812 Pinky3025 Brain1020 Slappy Squirrel2525 Totals Insurance Rates% Health 0.27 Workers Comp0.08 Unemployment 0.12 Sheet4 ProductCustomerQtr 1Qtr 2Qtr 3Qtr 4 Alice MuttonANTON$902.00 Alice MuttonBERGS$312.00 Alice MuttonBOLID$1,170.00 Alice MuttonBOTTM$1,170.00 Alice MuttonERNSH$1,123.20$2,607.15 Alice MuttonGODOS$280.80 Alice MuttonHUNGC$62.40 Alice MuttonPICCO$1,560.00$936.00 Alice MuttonRATTC$592.80 Alice MuttonREGGC$741.00 Alice MuttonSAVEA$3,900.00$789.75 Alice MuttonSEVES$877.50 Alice MuttonWHITC$780.00 Aniseed SyrupALFKI$60.00 Aniseed SyrupBOTTM$200.00 Aniseed SyrupERNSH$180.00 Aniseed SyrupLINOD$544.00 Aniseed SyrupQUICK$600.00 Aniseed SyrupVAFFE$140.00 Boston Crab MeatANTON$165.60 Boston Crab MeatBERGS$920.00 Boston Crab MeatBONAP$248.40$524.40 Boston Crab MeatBOTTM$551.25 Boston Crab MeatBSBEV$147.00 Boston Crab MeatFRANS$18.40 Boston Crab MeatHILAA$92.00$1,104.00 Boston Crab MeatLAZYK$147.00 Boston Crab MeatLEHMS$515.20 Boston Crab MeatMAGAA$55.20 Chartreuse verteALFKI$283.50 Chartreuse verteBERGS$86.40$324.00 Chartreuse verteBLAUS$90.00 Chartreuse verteERNSH$2,106.00 Chartreuse verteFOLKO$288.00 Chartreuse verteHUNGO$43.20 Chartreuse verteMAGAA$30.60 Chartreuse verteOTTIK$180.00 Chartreuse vertePRINI$216.00 Chartreuse verteQUEDE$360.00 Chartreuse verteTRAIH$180.00 Chartreuse verteWARTH$288.00 Escargots de BourgogneFRANK$397.50 Escargots de BourgogneHUNGO$519.40 Escargots de BourgogneLINOD$298.13 Escargots de BourgogneOLDWO$397.50 Escargots de BourgogneRICSU$265.00 Escargots de BourgogneSAVEA$119.25 Escargots de BourgogneWARTH$79.50 KonbuBSBEV$48.00 KonbuCHOPS$36.00 KonbuCONSH$4.80 KonbuERNSH$66.30 KonbuHILAA$403.20 KonbuKOENE$102.00 KonbuOTTIK$60.00 KonbuVINET$24.00 KonbuWARTH$60.00 KonbuWELLI$8.64 Longlife TofuFRANS$50.00 Longlife TofuHILAA$128.00 Longlife TofuMEREP$240.00 Longlife TofuQUICK$120.00 Longlife TofuVICTE$112.50 Longlife TofuWARTH$350.00 Louisiana Fiery Hot Pepper SauceBONAP$199.97 Louisiana Fiery Hot Pepper SauceERNSH$820.95$1,299.84 Louisiana Fiery Hot Pepper SauceFRANR$252.60 Louisiana Fiery Hot Pepper SauceFURIB$268.39 Louisiana Fiery Hot Pepper SauceHANAR$682.02 Louisiana Fiery Hot Pepper SauceHUNGO$421.00$842.00 Louisiana Fiery Hot Pepper SauceLAMAI$226.80 Ravioli AngeloANTON$87.75 Ravioli AngeloAROUT$780.00 Ravioli AngeloBLAUS$78.00 Ravioli AngeloBONAP$204.75 Ravioli AngeloBSBEV$117.00 Ravioli AngeloPICCO$390.00 Ravioli AngeloTOMSP$187.20 Ravioli AngeloWARTH$312.00 Vegie-spreadALFKI$878.00 Vegie-spreadERNSH$2,281.50 Vegie-spreadFOLIG$1,317.00 Vegie-spreadHUNGO$921.37 Vegie-spreadMORGK$263.40 Vegie-spreadPICCO$395.10 Vegie-spreadWHITC$842.88