CIS need help gruftt
fiendin
e_ch07_expv2_a1.zipE_CH07_EXPV2_A1_Instructions.docx
Office 2013 – myitlab:grader – Instructions Exploring - Excel Chapter 7: Assessment Project 1
Specialized Functions
Project Description: In the following project, you will perform sales analysis, calculate summary data using database functions, and complete an amortization table.
Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Download and open the file named exploring_e07_grader_a1.xlsx, and then save the file as e02c1Sales_LastFirst, replacing LastFirst with your name. 0 2 Click the Sales Data by Agent worksheet and enter a nested function in cell H9 the Bonus column. If the employee is international and sold over $200,000 they receive 5% bonus, all other employees receive 3%. Hint: On the Sales Data by Agent worksheet, in cell H9, enter =IF(AND(F9>=200000,G9="international"),$A$4,$A$3) and then press ENTER. 10 3 Using the appropriate cell referencing, copy the function down the column. Hint: Double-click the fill handle in cell H9. 7 4 Type Ron in cell B24. 4 5 Type Q1 in cell B25. 4 6 Enter a nested function in cell B26 that uses the cells B24 and B25 to return a specific sales record. Hint: In cell B26, enter =INDEX(B9:H21,MATCH(B24,A9:A21,0),MATCH(B25,B8:H8,0)) and then press ENTER. 10 7 Click the Individual Awards worksheet and enter conditions in the Criteria Range for international sales reps that made $250,000 or more in sales. Hint: On the Individual Awards worksheet, in cell F3, enter >=250000. In cell G3, enter International. 10 8 Perform an advanced filter based on the criteria range. Set the filter to copy the new data into row 22. Hint: On the DATA tab, in the Sort & Filter group, click Advanced. 10 9 Enter a database function to calculate the total number of international sales rep in cell J8. Hint: In cell J8, enter =DCOUNTA(A6:G19,G6,G2:G3) and then press ENTER. 12 10 Enter a database function to calculate the highest international sales dollar in cell J9. Hint: In cell J9, enter =DMAX(A6:G19,F6,G2:G3) and then press ENTER. 3 11 Click the Acquisition worksheet and then insert a function in cell E2 to calculate the loan amount based on the loan parameters. Hint: Click the Acquisition worksheet tab and in cell E2, enter =B2-B3 and then press ENTER. 4 12 Enter a formula in cell E3 to calculate the total number of periods. Hint: In cell E3, enter =B4*B5 and then press ENTER. 2 13 Enter a formula in cell E4 to calculate the periodic monthly rate. Hint: In cell E4, enter =B6/12 and then press ENTER. 2 14 Enter a function in cell E5 to calculate the monthly payment. Modify the function to ensure that the result is a positive number. Hint: In cell E5, enter =PMT(E4,E3,-E2) and then press ENTER. 2 15 Enter a function in cell E6 to calculate the total interest paid after five payments. Modify the function to ensure that the result is a positive number. Hint: In cell E6, enter =-CUMIPMT(E4,E3,E2,A11,A15,0) and then press ENTER. 2 16 Complete the loan amortization table for the first five payments only. In cell A11, enter 1. In cell B11, create a relative reference to cell B7 and in cell C11, create a relative reference to cell E2. Use the DATE function to complete the Payment Date column and financial functions for the Interest Paid and Principal Payment columns. In cell F11, enter =C11-E11. In cell C12, create a relative reference to cell F11. Note: Be sure to only complete the table through row 15. Hint: In cell A11, enter 1 and then press TAB. In cell B11, enter =B7 and then press TAB. In cell C11, enter =E2 and then press TAB. In cell D11, enter =IPMT(E$4,A11,E$3,-E$2) and then press TAB. In cell E11, enter =PPMT(E$4,A11,E$3,-E$2) and then press TAB. In cell F11, enter =C11-E11 and then click cell B12. In cell B12, enter =DATE(YEAR(B11),MONTH(B11)+1,DAY(B11)) and then press TAB. In cell C12, enter =F11 and then press ENTER. Use the Fill handle in each column to complete the table. 18 17 Save the file making sure the worksheets are in the following order: Sales Data By Agent, Individual Awards, and Acquisition. Close Excel. Submit the file as directed. 0 Total Points 100
Updated: 07/17/2013 1 E_CH07_EXPV2_A1_Instructions.docx
exploring_e07_grader_a1.xlsx
Sales Data By Agent
| Bonus | Region | ||||||
| 3% | Domestic | ||||||
| 5% | International | ||||||
| 2015 Sales Total By Quarter | |||||||
| Sales Rep | Q1 | Q2 | Q3 | Q4 | Total | Location | Bonus |
| Ron | $ 29,911.00 | $ 92,249.00 | $ 46,475.00 | $ 33,947.00 | $ 202,582.00 | International | |
| Nick | $ 32,752.00 | $ 30,222.00 | $ 43,997.00 | $ 93,277.00 | $ 200,248.00 | International | |
| Sally | $ 36,991.00 | $ 54,102.00 | $ 63,914.00 | $ 42,642.00 | $ 197,649.00 | Domestic | |
| Susan | $ 50,087.00 | $ 25,179.00 | $ 64,912.00 | $ 68,875.00 | $ 209,053.00 | Domestic | |
| Bob | $ 52,923.00 | $ 62,673.00 | $ 63,635.00 | $ 57,410.00 | $ 236,641.00 | Domestic | |
| Mark | $ 59,678.00 | $ 70,934.00 | $ 78,410.00 | $ 32,994.00 | $ 242,016.00 | Domestic | |
| Swathi | $ 66,385.00 | $ 74,270.00 | $ 36,165.00 | $ 76,548.00 | $ 253,368.00 | Domestic | |
| Mike | $ 66,936.00 | $ 72,838.00 | $ 60,479.00 | $ 63,324.00 | $ 263,577.00 | Domestic | |
| Rick | $ 74,507.00 | $ 94,178.00 | $ 41,391.00 | $ 27,235.00 | $ 237,311.00 | Domestic | |
| Rich | $ 76,889.00 | $ 49,266.00 | $ 64,225.00 | $ 55,410.00 | $ 245,790.00 | Domestic | |
| Jill | $ 90,515.00 | $ 29,238.00 | $ 30,973.00 | $ 32,145.00 | $ 182,871.00 | Domestic | |
| John | $ 97,426.00 | $ 43,061.00 | $ 26,122.00 | $ 83,391.00 | $ 250,000.00 | Domestic | |
| Greg | $ 98,094.00 | $ 47,398.00 | $ 80,755.00 | $ 40,446.00 | $ 266,693.00 | International | |
| Look up | |||||||
| Sales rep name | |||||||
| Quarter | |||||||
| Amount sold |
Individual Awards
| Criteria | ||||||||
| Sales Rep | Q1 | Q2 | Q3 | Q4 | Total | Location | ||
| Sales Rep | Q1 | Q2 | Q3 | Q4 | Total | Location | Totals | |
| Ron | $ 29,911.00 | $ 92,249.00 | $ 46,475.00 | $ 33,947.00 | $ 202,582.00 | International | Total Number of Domestic reps | |
| Nick | $ 32,752.00 | $ 30,222.00 | $ 43,997.00 | $ 93,277.00 | $ 200,248.00 | International | Total Number of International reps | |
| Sally | $ 36,991.00 | $ 54,102.00 | $ 63,914.00 | $ 42,642.00 | $ 197,649.00 | Domestic | Max International Sales | |
| Susan | $ 50,087.00 | $ 25,179.00 | $ 64,912.00 | $ 68,875.00 | $ 209,053.00 | Domestic | Max Domestic Sales | |
| Bob | $ 52,923.00 | $ 62,673.00 | $ 63,635.00 | $ 57,410.00 | $ 236,641.00 | Domestic | ||
| Mark | $ 59,678.00 | $ 70,934.00 | $ 78,410.00 | $ 32,994.00 | $ 242,016.00 | Domestic | ||
| Swathi | $ 66,385.00 | $ 74,270.00 | $ 36,165.00 | $ 76,548.00 | $ 253,368.00 | Domestic | ||
| Mike | $ 66,936.00 | $ 72,838.00 | $ 60,479.00 | $ 63,324.00 | $ 263,577.00 | Domestic | ||
| Rick | $ 74,507.00 | $ 94,178.00 | $ 41,391.00 | $ 27,235.00 | $ 237,311.00 | Domestic | ||
| Rich | $ 76,889.00 | $ 49,266.00 | $ 64,225.00 | $ 55,410.00 | $ 245,790.00 | Domestic | ||
| Jill | $ 90,515.00 | $ 29,238.00 | $ 30,973.00 | $ 32,145.00 | $ 182,871.00 | Domestic | ||
| John | $ 97,426.00 | $ 43,061.00 | $ 26,122.00 | $ 83,391.00 | $ 250,000.00 | Domestic | ||
| Greg | $ 98,094.00 | $ 47,398.00 | $ 80,755.00 | $ 40,446.00 | $ 266,693.00 | International | ||
| Sales Rep | Q1 | Q2 | Q3 | Q4 | Total | Location |
Acquisition
| Input Area | Summary Calculations | ||||
| Facility costs | $ 720,000.00 | Loan Amount | |||
| Down Payment | $ 250,000.00 | No. Periods | |||
| # of Pmts per Year | 12 | Monthly Rate | |||
| Years | 30 | Monthly Payment | |||
| APR | 5.25% | Total Interest Paid | |||
| 1st Payment Date | 3/20/15 | ||||
| Payment # | Payment Date | Beginning Balance | Interest Paid | Principal Payment | Ending Balance |
