Reference no: EM132298722

Computer Applications in CSET Assignment - 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.

Attachment:- Assignment Files.rar