String Manipulations: 1] Cells 32-815 => 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 32-315). 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 017-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 DlS-FlB 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 DB) 7] Cells DZO-FZO => Calculate minimum value for columns using an excel function 8] Cells D21-F21 => Calculate maximum value for columns using an excel function ASU ACC 350 - Internal Reporting Garverick - Spring 2018 Excel Assignment #1 High-Low Calculations (for Inspection Hrs}: 9) Cells 62-615 => calculation where if row contains minimum hours, then put 55$ associated with these hours, blank (\"") otherwise 10) Cells H2-H15 => calculation where if row contains maximum hours, then putSSS associated with these hours, blank (\"\") otherwise 11} Cell 620 => assume that in case multiple minimum hours exist we will take the average of the $55 associate with them so calculate the average of column here which we will use later in the high-low formula for the minimum 555 12} Cell H21 => assume that in case multiple maximum hours exist we will take the average of the $55 associate with them so calculate the average of column here which we will use later in the high-low formula for the maximum 555 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 325-025). 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 l'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. S on Y (vertical) Axis and Hours on X (horizontal) Axis d. Whinear) Regression #1: Inspection Hours (IV) on Inspection Cost (DV) 16) Create Regression and output results to cell A1 of \"Regr-lnspHrs\" 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: ll Batches (IV) on Inspection Cost (DV) 18) Create Regression and output results to cell A1 of " egr-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): ll Batches (IV) on Inspection Cost (DV) 20) Create Regression and output results to cell A1 of "W\" tab 21) Cells A37-C37 8: 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 Bl-DI on \"w" 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 Home - - ' J ' E '. loalibrimody) an VI'AAVAV' l: lJWrapTexl r . W. \"5\"" g: 12%?v [0ut ' H" Delete ' Paste ' B I U v H Merge&Center v $ 7 % ) {\"0 0 Candmcnal Format Cell ..... Serial 6 ' H 7 i5 Formatting as Table Styles I Format' ' ' Filter F30 :| x ~/ c a c n c 2... rum (in mm mm. m Mull: any mmuoumhmm mnomnmm nmwonmmsm nuuommmm nmuonmnsm nmuomumssim \"maouinmssm .maomnmm wmnoumhmm aunommmm nmwoummxm uuuoummmm mmuommssm nmuommsm umaouinmsumw'm amucmmmm Mmi'oumhsm mnommmm wmuoumhmm quoumnsxm mwonmnm nmuommmm xmn'ouhmmm amuommmm ssm-Ioummssmwu mnomnsssm umuonuhmm aunomnmm mm mm mm mime mum slum mm mm; mm mm mm wan-m W SESEE mm. mm. mm. m (a) M (an) m (an) MM) Amanda) Amman) m. um um Eu )4- u. wmmmmumn. "mm -Ic a: m l Ingmar-murmur mum -u w m... l Wmmrml' v. : m... r a: 5v: 5 l Scatterplot RegrlnspHrs RegrNumBatches RegrMult TDisl + IO ' LastnameiFirstnamejectionXXXXXACCSEQExcelExercise'l Page Layout Formulas Data Review View '96 ii m vim = :iwm Eli; *'E/i' 32:22:: C? [Brgvi v & v A v E E E E 9E : M an: t v f *- '0\" Conditional Format Cell ..... H - ' ' H _ erge en er $ A) ,' '00 IN) Formatting asTable Styles I Format' ' A1 : X \\/ t Output A B c D E F m I x ,_ Z 2 o 'v O :0 U! -| c mmumuwalI H o H H H N H u: H a H u- H ax H \\l H on H u N O N H N N N w N a. 25 i i i i 4 D B Data Scatterplot RegrlnspHrs RegrNumBatches RegrMuit TDist + Ready E + IO ' Lastname_Firstname_SectionXXXXXACC350ExcelExercise'1 Insert Page Layout Formulas Data Review View E v '3' W 11 v 'A' Av' '2 :aWrapText _eneral iv iv I?" 'e {e I e 4 we e A e {:2 e ewe: eew aeeeeeeeee t; e es me; am; 52;; A A1 v X v' t Output A B C D E F I Output II m :I: x l E Z 0 'v D :u u- lbwmm-thH H D H H H N H u) H a; H In H m H \\J H on H ID N O N H N N N In I a N m { k 9 Data Scatterplot RegrlnspHrs RegrNumBatches RegrMult HTDist I + ' Ready [H] - stnamejirstnameectionXXXXXA OExcelExerciseW Page Layout Formulas Data Review View ~ 3% W3... 11 w Aw .= = =wvm~x Eh 3% w W 2'5 L V & ' J 7'7 J g" Delete V EV ' v = = =' = = v v 0 'L ' H 'H' ' Paste 6 H v A ' \\= = :1\": =l 'j Merge&Center w .00 0.0 (Figpgggagl Legs; 8:21:25 I Formatv 'V 7 A1 $ X v f3: Output A a C D E F G H I J K L M N o P Q R s T U v 1|0utput II 2 3 4 5 5 7 B 9 .o .1 .2 .3 .4 .5 .6 .7 .8 .9 [0 :1 I2 [3 :4 I5 4 } Data Scatterplot RegrlnspHrs RegrNumBatches l RegrMult TDist ' + ' Ready E O + Lastnamef1rstnamejectionXXXXXACCSSOi xcelExerc15e'1 Insert Page Lay Formulas Data Review View . 3S Calibrimody) . 11 . AA Av1 1: = = .39" 311.1 1:)WrapText General . r /,'.1 gmser\" 2' A?' i? v , \\b 1 7 4.)" '28)] 1" Delete V E' Z v V\\v VEEEEE \" v 0 0.00 .... 6 1B 1 9 1 1 A 1 1 1* *1 MW _i$ ' A) ' 1L 2:112:22: .2111; 35:12. . r 5.21;? 013 $ x v' r 3.055 1 A 9 c D E F G H 1 J K L M N o P Q R s 1 u v w x 1 01: 90% 95% 99% 2 1 6.314 12.709 63.657 1 3 2 2.920 4.303 9.925 4 3 2.353 3.132 5.041 5 4 2.132 2.776 4.604 s 5 2.015 2.571 4.032 7 6 1.943 2.447 3.707 9 7 1.995 2.365 3493 9 a 1.860 2.306 3.355 ' 10 9 1.933 2.262 3.250 11 10 1.312 2.229 3.163 12 11 1.796 2.201 3.106 13 12 1.732 2.179] 3.055_| . 1 14 13 1.771 2.160 3.055 15 14 1.761 2.145 3.012 16 15 1.753 2.131 2.947 17 16 1.746 2.120 2.921 18 17 1.740 2.110 2.398 19 1s 1.734 2.101 2.073 20 19 1.729 2.093 2.861 21 20 1.725 2.086 2.845 . 22 30 1.697 2.042 2.750 ' 23 31 1.645 1.960 2.576 1 1 24 1 1 1 1 25 1 1 1 . . 1 1 1 1 b 1 Data 1 Scatterplot 1 RegrlnspHrs 1 RegrNumBatches 1 Regr-Mull TDist + 1 Ready Q + 100%