In this undertaking. you will make a worksheet that calculates gross revenues and committees for a used auto company. You will make maps that determine monthly payments. lookup committee rates. and cipher a fillip for the sales representative.
For the intent of rating the undertaking you are required to execute the undermentioned undertakings: Step| Instructions| Points Possible|
1| Start Excel. Open the downloaded Excel workbook named Exploring_e02_Grader_IR. xlsx. | 0| 2| In cell G5. come in a expression that calculates the province gross revenues revenue enhancement by multiplying the base cost of the auto ( in F5 ) by 7 % . Copy the expression down to cells G6: G16. | 11| 3| In cell H5. come in a expression that adds the gross revenues revenue enhancement ( in G5 ) to the basal monetary value of the auto ( in F5 ) . Copy the expression down to cells H6: H16. | 11| 4| In cell I5. come in a map to cipher a monthly payment for purchasers. The map will mention to the one-year involvement rate ( in D21 ) divided by 12. a loan term of 48 months. and the entire cost of the auto ( in H5 ) . Mention to the one-year involvement rate ( D21 ) as an absolute cell mention. Copy the map down to cells I6: I16. | 11| 5| In cell J5. make a VLOOKUP map that returns the salesperson committee that corresponds with the base cost of the auto ( in F5 ) . Use the scope $ C $ 24: $ D $ 28 as the Lookup Table. and return the committee rate from Column 2. | 10| 6| Copy the VLOOKUP map down to cells J6: J16. | 5|
7| In cell G19. come in a map to sum cells H5: H16. | 7| 8| In cell G20. come in a map to average the cells in the scope H5: H16. | 7| 9| In cell G21. come in a map to happen the lowest value in the scope H5: H16. | 7| 10| In cell G22. come in a map to happen the highest value in the scope H5: H16. | 6| 11| In cell G23. come in a map that will return the value of today’s day of the month. | 5| 12| In cell G24. come in an IF map that will cipher a fillip for the parttime gross revenues agents. If the value in cell G19 ( Total for all gross revenues ) is greater than 250000. so multiply G19 by. 015. Otherwise. the map will return a value of 0 ( i. e. no fillip ) . | 10| 13| Format cell G24 with the Currency format and two denary topographic points. | 4| 14| In cell C17. come in a map that counts the figure of entries in the scope C5: C16. | 6| 15| Save the workbook. Close the workbook and so issue Excel. Submit the workbook as directed. | 0| | Total Points| 100|