Computer Science Homework Help
San Diego State University Electronics Shop Inventory Worksheet
I’m working on a operating systems multi-part question and need an explanation and answer to help me learn.
OBJECTIVE: GAIN EXPERIENCE WORKING WITH FUNCTIONALITY, MORE COMPLEX FORMULAS (CONCAT, PROPER, UPPER, LOWER, ROUNDUP, ETC). ALSO, LEARN ABOUT ANALYZING DATA WITH SLICERS AND A PIVOT TABLE.
DIRECTIONS: FOLLOW ALL OUTLINED STEPS BELOW. THESE ARE ALSO INSIDE OF THE FILE PROVIDED TOO. PLEASE READ THEM CAREFULLY. EVERYTHING YOU NEED TO COMPLETE THIS EXERCISE IS PROVIDED HERE.
- THE FILE TO START WITH – YES, PROVIDED HERE —->
- DIRECTIONS TO FOLLOW – YES, PROVIDED
WHAT TO SUBMIT: SUBMIT THE COMPLETED WORKBOOK. SAVED WITH YOUR NAME AT THE END AND IN .XLSX TYPE ONLY!
Directions: Please follow ALL STEPs in Order. Read carefully. They are detailed. | |
To Do’s – Sample Set – Inventory Sheet | |
Step 1 | apply a sheet tab color (any one) |
Step 2 | merge & center title – cell styles/title |
Step 3 | date – replace with “=today()” formula |
Step 4 | replace shaded column with your own Business items data. Remove shading |
Step 5 | add in your own pricing, qty, and type (according to your business items). Remove shading |
Step 6 | format data as a table. Pick a selection – Home/styles/format as table |
Step 7 | correct error in column A5-A14 |
Step 8 | name ranges – formula/defined names/create from selection |
Step 9 | totals column F – enter in a “named ranges” formula. Hint: “=Price*Qty” |
Step 10 | add in formula in col B at end “=counta(enter in range)” |
Step 11 | create a copy of this sheet tab & name “Analyzed-Inventory” |
To Do’s – Analyzed – Inventory Sheet | |
Step 1 | apply a sheet tab color (any one) |
Step 2 | add an “Item” slicer. Hint: insert/filters/slicer |
Step 3 | add a total rows in A15, 16, 17, 18, & 19 (cell styles home tab) |
Step 4 | D15-E15 enter in a sum formula for “price & qty” columns |
Step 5 | B16 type in “Rounded” |
Step 6 | B17 type in “Max Price” |
Step 7 | B18 type in “Min Price” |
Step 8 | B19 type in “Average Price” |
Step 9 | D16-E16 RoundUp formula for “price & qty” columns |
Hint: “=RoundUp(d15,0)” etc. | |
Note: you are rounding up the data from D16.E16 | |
Step 10 | D17 enter in Max formula |
Step 11 | D18 enter in Min formula |
Step 12 | D19 enter in Average formula |
Step 13 | go to any cell in the table. Then add a pivot table. |
Hint: insert/charts/pivotchart/pivot chart & pivot table | |
Step 14 | select “existing worksheet” option & select a blank range of cells next to table somewhere. |
Step 15 | hit “ok” and check off some areas like “sum of Price and sum of Qty” etc |
Step 16 | experiment with filtering your data via “row labels” on pivot table |
To Do’s – Suppliers Sheet | |
Step 1 | apply a sheet tab color (any one) |
Step 2 | A1 merge & center and apply Title style. Hint: cell styles/title |
Step 3 | follow all directions in rows B3-J4 |
Step 4 | Do not forget to HIDE columns B, C, D, F, G |
Step 5 | format as a table. Hint: home/styles/format as table |
Step 6 | insert a slicer based on NAME column |
Step 7 | when finished, please HIDE ALL THESE DIRECTIONS (COLUMNS G & H! |