Assignment
Overview
• The purpose of this assignment is to get an understanding of basic to intermediate foundations of spreadsheet design and usage (e.g., formulas, functions, graphs, regression calculations, etc.).
• The spreadsheet is set up with the data already in place. Certain cells and worksheets are protected (so that you cannot change them) and other cells are unprotected and highlighted/colored specifically for you to perform the required operations as directed below.
• You have the necessary background from prior classes as well as the ability to use the internet to lookup and figure out how to solve the requirements below. Some steps require going through certain steps or processes in Excel to create graphs/scatterplots and calculate regressions. Other things require using formulas and functions. Make use of Google, YouTube, Excel help, etc. to figure out how to do parts of this exercise.
• Everything should be done using an Excel process, function, calculation or formula referencing other cells as necessary. Appropriate cell formatting should be used (e.g., $ sign, # of decimal spaces, etc.)
o None of your answers should involve "Hard Coding" (i.e., entering numbers in cells instead of referencing on cells and performing calculations) data in the colored cells.
o Note that if you cannot figure out how to perform a certain step, you may need to "Hard Code" in values so that you can do the additional required steps (note that you will be knocked off points for this).
o Use ranges in your totals calculations (e.g., sums, counts, averages, minimums, maximums, etc) that include/consider "anchor rows" (one row above the beginning of data (i.e., header labels) and one below (i.e., dashes)) so that these calculations are flexible (will expand to include) if you insert rows of data in future analysis.
Requirements(all the cells mentioned below are located on the "Data" tab unless otherwise specified):
String Manipulations:
1) Cells B2-B15 => Calculate the four digit year using the same "relative" formula in all cells base on the data in cells A2-A15 (DO NOT TOUCH/CHANGE THE DATA in cells A2-A15; use formulas in B2-B15).
2) Cells C2-C15 => Calculate the "name" (e.g., "January" for 01, "February" for 02, etc.) based on the data in cells A2-A15 (Hint: this is tougher; this can be done without using logic like "if "Value" = "01" then "January" else if "Value" = "02" then "February" else...; make sure to use formulas in C2-C15)
Column Calculations (remember to include "anchor" rows in your formulas in case a data row is inserted):
3) Cell B17 =>Compute the number of rows of data
4) Cells D17-F17 => Calculate column totals
5) Cells D18-F18 => Calculate average for columns using an excel function
6) Cells D19-F19 => Compute average for columns to verify Cells D18-F18 by dividing column totals by the row count (hint: you should be able to create one formula using a $ (absolute) in cell reference in first column and copy it to other two columns instead of manually creating formula more than one time; see Problem 2-28 solution, Cell D3)
7) Cells D20-F20 => Calculate minimum value for columns using an excel function
8) Cells D21-F21 => Calculate maximum value for columns using an excel function
High-Low Calculations (for Inspection Hrs):
9) Cells G2-G15 => calculation where if row contains minimum hours, then put $$ associated with these hours, blank ("") otherwise
10) Cells H2-H15 => calculation where if row contains maximum hours, then put $$ associated with these hours, blank ("") otherwise
11) Cell G20 => assume that in case multiple minimum hours exist we will take the average of the $$ associate with them so calculate the average of column here which we will use later in the high-low formula for the minimum $$
12) Cell H21 => assume that in case multiple maximum hours exist we will take the average of the $$ associate with them so calculate the average of column here which we will use later in the high-low formula for the maximum $$
13) Cells A25-C25 =>use formulas from data above to compute the cost equations using the high-low method (where you should be able to input a number of inspection hours into the red cell D25 and the Total Cost estimate will result in A25; hint => start with slope calculation in C25, then calculate FC in B25 then total cost in A25 based on cells B25-D25).
Scatterplot
14) Based on data in columns D and E (Inspection Cost and Inspection Hours) create a Scatterplot. Cut and copy the scatterplot from this tab into cell A1 of the "Scatterplot" tab.
15) From here, make sure that the Scatterplot has (you may have to add):
a. Chart Title,
b. Appropriate Labels on both Axis Titles,
c. $ on Y (vertical) Axis and Hours on X (horizontal) Axis
d. Trendline (Linear)
Regression #1: Inspection Hours (IV) on Inspection Cost (DV)
16) Create Regression and output results to cell A1 of "Regr-InspHrs" tab
17) Cells A29-C29 => Use formulas to reference output and create cost function (where you should be able to input a number of inspection hours into the red cell D29 and the Total Cost estimate will result in A29).
Regression #2: # Batches (IV) on Inspection Cost (DV)
18) Create Regression and output results to cell A1 of "Regr-NumBatches" tab
19) Cells A33-C33 => Use formulas to reference output and create cost function (where you should be able to input a number of inspection hours into the red cell D33 and the Total Cost estimate will result in A33).
Regression #3 (Multiple): # Batches (IV) on Inspection Cost (DV)
20) Create Regression and output results to cell A1 of "MultRegr" tab
21) Cells A37-C37 & E37 => Use formulas to reference output and create cost function (where you should be able to input a number of inspection hours into the red cells D37 and F37 and the Total Cost estimate will result in A37).
Confidence Interval
22) Cell C39 => Create a drop-down box that validates data to only 3 values: 90%, 95% and 99% (hint: use data validation and cells B1-D1 on "TDist" tab in list of valid data entries)
23) Cell A41=> Create a formula to reference the result of your Multiple Regression equation (Cell A37)
24) Cell C41 =>Create a formula to lookup correct value in T-table presented on the "TDist" tab (Hint: this is challenging; use VLOOKUP and then use If statement logic based on value in C39 to determine what column of VLOOKUP to find result in)
25) Cell D41 =>Create a formula to reference the Standard Error of the Multiple Regression output
26) Cells A42 and C42 =>Create formulas to compute the Confidence intervals.
Attachment:- Excele_Exercise.xlsx