8. In cell K4, write a formula that will look up the low interest loan entitlement for sales rep Bud Fox, based on his total sales in cell J4. You must use the named range you created in Step 6 in your formula. Copy the formula to the cell range K5 through K12.

9. In cell E4, write a formula that determines the employment status of the sales rep Bud Fox (cell D4). If the sales rating of Bud is equal to Oh-Oh, then his employment status is displayed as Terminated, otherwise his employment status is displayed as Employed. Copy the formula to the cell range E5 through E12. Hint: You will need to use the IF function in your formula to answer this step correctly.

10. In cell L4, write a formula to determine the monthly loan payment of sales rep Bud Foxs low interest loan entitlement in cell K4. Make sure your formula displays a positive monthly loan payment. Additional parameters about the loan are provided in cells P11 through P13 you must use appropriate references to these in your formula. Copy the formula to the cell range L5 through L12. Hint: You will need to use the PMT function in your formula to answer this step correctly.

File Insert Page Layout Home Formulas Data Review View Help Calculate Now fix -68 Calculation Define Name - SE Name Use in Formula Manager Create from Selection Defined Names E, Trace Procedents Pe Show Formula Trace Dependents Error Checking Remove Arrows Evaluate formula formule Watch Window Insert Function 12 TA 19 le AutoSum Recently Financial Logical Text Date & Lookup & Math & More Used Time Reference - Thig Functions Function Library vX fi Options Calculate Shest Calculation F16 M N P 2 D G H A 1 Get Well Soon Pharmaceuticals Sales Rep Sales 2 Employment Status First Name Bud Giovanni Jamie Jordan Penny Ray Rudy Tommy Willy 3 Last Name 4 Fax 5 Ribisi 5 Randall 7 Belfort Hofstadter Kroc 0 Russo 1 Wilhelm 2 Laman 3 Full Name Bud Fox Giovanni Ribis Jamie Randall Jordan Relfort Penny Hofstadter Ray Kroc Rudy Russo Tommy Wilhelm Willy Loman Sales Rating WooHoo WooHoo Silver Gold Wooloo Gold Bronze Oh-oh Bronze Q1 273,643 345,897 55,153 235,218 642,082 133,904 23.910 9,087 56,841 Q2 333,324 446,050 75,145 271,094 413,890 122,234 28,871 7,841 66,640 03 477,519 423,501 119,736 134,219 457,518 116,870 30,651 9,900 61811 04 326,566 305,069 179,274 189,135 596,248 165,041 20484 5,009 54,488 Total Sales 1,411,052 1,520,517 429,308 830,166 2,109,738 538,049 103,916 31.837 239,780 Loan Low Interest Loan Monthly Loan Entitlement Payment 100.000 100,000 15,000 40,000 100.000 #N/A #N/A #N/A #N/A Lookup Table Low Interest Sales Loan Sales Range Rating Entitlement 5 Oh-oh - $ 50 000 Bronze 5,000 250.000 Silver 15,000 500,000 Gold 40,000 1,000,000 WooHoo 100,000 Loan Parameters Annual Interest Rate: Loan Length (yrs): Payments per Year: 0.10% 5 12 4 5 Number of WooHoo rated sales reps: Average total sales of WooHoo rated sales reps: 3 1,680,436 o Grading Sales Rep Performance Purchases LIE le dy 4C Sunny OL ENG US