20Excel%20Assignment.pdf Crain's Consulting Firm Inputs and Constants Requires a Date Formula "Today" Today: 7/17/2014 Requires Year Years Threshold 5 Frac Function Requires a VLOOKUP (3-points) High Year Rate: 3.25% (2-points) Low Year Rate: 2.00% SOC Rating Bonus Raise New Salary 5 Current Years Rating Date Hired Salary Employed Score 4/1/2004 $ 50,000 110.79 7/15/2012 $ 75,250 2.01 3.5 10/31/2008 $ 67,250 5.71 4.2 9/8/2003 $ 45,980 10.86 2 3/14/2011 $ 58,750 3.34 1.5 6/18/2010 $ 61,000 4.08 4.5 Requires Addition (1-point) Requires an IF Statement (3-points) Bonus 1 Bonus Data Rating 1 $ 2 $ 3 $ 4 $ 5 $ This is a table that is a part of your VLOOKUP (1-point) N 3 4 5 100 250 500 1,000 18 x & fx B D E F G 1 Your Name's Consulting Firm Rating Bonus Ralse New Compensation Rating Score 5 3.5 4.2 2 1.5 4.5 2 3 Inputs and Constants 4 Today: 10/31/20 5 Years Threshold: 5 6 High Year Rate: 3.25% 7 Low Year Rate: 2.00% 8 9 Current Years 10 Date Hired Salary Employed 11 4/1/04 $ 50,000 12 7/15/12 $ 75,250 13 10/31/08 $ 67,250 14 9/8/03 $ 45,980 15 3/14/11 $ 58,750 16 6/18/10 $ 61,000 17 18 19 Bonus Data 20 Rating Bonus 21 1 $ 22 2 $ 100 23 3 $ 250 24 4 $ 500 25 5 $ 1,000 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40