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.
Define Name fx - calculate Now More AutoSum Recently Financial Logical Text Date & Lookup & Math & Used Time Reference Trig Functions Insert Function Trace Precedents We Show Formulas Trace Dependents A Ertor Checking FRemove Arrows - Evaluate Formula Formula Auditing A 68 Lod Watch Window Calculation calculats Shoot Name Use in Formula Manager Create from Selection Defined Names Ed Options Function Library Calculation M19 IX Xfx c B E D F G H 1 P M N 1 Get Well Soon Pharmaceuticals Sales 2 Sales Rep Loan Low Interest Loan Monthly Loan Entitlement Payment Q1 273,643 345,897 55,153 235,718 642,082 133,904 23.910 9,087 55,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 61,811 04 326,566 305 059 179 274 189,135 596,248 165.041 20,484 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 Lookup Table Low Interest Sales Loan Sales Range Rating Entitlement $ Oh-oh $ 50,000 Bronze 5,000 250,000 Silver 15,000 500,000 Gold 40,000 1,000,000 WooHoo 100,000 0.10% Loan Parameters Annual Interest Rate: Loan Length (yrs) Payments per Year: 12 3 3 1,680,436 Sales Employment 3 Last Name First Name Full Name Rating Status 4 Fox Bud Bud Fox WooHoo 5 Ribisi Giovanni Giovanni Ribisi WooHoo 6 Randall Jamie Jamie Randall Silver 7 Belfort Jordan Jordan Belfort Gold a Hofstadter Penny Penny Hofstadter Woohoo 9 Kroc Ray Ray Kroc Gold 10 Russo Rudy Rudy Russo Bronze 11 Wilhelm Tommy Tommy Wilhelm Oh-oh 12 Loman Willy Willy Loman Bronze 13 14 Number of WooHoo rated sales reps: 15 Average total sales of WooHoo rated sales reps: 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Grading Sales Rep Performance Purchases Ready 30 Sunny W ENG US OP Define Name fx - calculate Now More AutoSum Recently Financial Logical Text Date & Lookup & Math & Used Time Reference Trig Functions Insert Function Trace Precedents We Show Formulas Trace Dependents A Ertor Checking FRemove Arrows - Evaluate Formula Formula Auditing A 68 Lod Watch Window Calculation calculats Shoot Name Use in Formula Manager Create from Selection Defined Names Ed Options Function Library Calculation M19 IX Xfx c B E D F G H 1 P M N 1 Get Well Soon Pharmaceuticals Sales 2 Sales Rep Loan Low Interest Loan Monthly Loan Entitlement Payment Q1 273,643 345,897 55,153 235,718 642,082 133,904 23.910 9,087 55,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 61,811 04 326,566 305 059 179 274 189,135 596,248 165.041 20,484 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 Lookup Table Low Interest Sales Loan Sales Range Rating Entitlement $ Oh-oh $ 50,000 Bronze 5,000 250,000 Silver 15,000 500,000 Gold 40,000 1,000,000 WooHoo 100,000 0.10% Loan Parameters Annual Interest Rate: Loan Length (yrs) Payments per Year: 12 3 3 1,680,436 Sales Employment 3 Last Name First Name Full Name Rating Status 4 Fox Bud Bud Fox WooHoo 5 Ribisi Giovanni Giovanni Ribisi WooHoo 6 Randall Jamie Jamie Randall Silver 7 Belfort Jordan Jordan Belfort Gold a Hofstadter Penny Penny Hofstadter Woohoo 9 Kroc Ray Ray Kroc Gold 10 Russo Rudy Rudy Russo Bronze 11 Wilhelm Tommy Tommy Wilhelm Oh-oh 12 Loman Willy Willy Loman Bronze 13 14 Number of WooHoo rated sales reps: 15 Average total sales of WooHoo rated sales reps: 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 Grading Sales Rep Performance Purchases Ready 30 Sunny W ENG US OP
