ComputerScienceExpert
$18/per page/
Any help would be great regarding if functions. Thank you!
​
Bonus Parameters
Threshold
Domestic
International $ 200,000
3%
5% 2015 Sales Total By Quarter
Sales Rep
Ron
Nick
Sally
Susan
Bob
Mark
Swathi
Mike
Rick
Rich
Jill
John
Greg
Total of All Reps Winter
$
29,911
$
32,752
$
36,991
$
52,923
$
50,087
$
59,678
$
66,385
$
66,936
$
74,507
$
76,889
$
90,515
$
67,426
$
98,094
$
803,094
Match-Index Period
Column
Amount sold
Amount sold (nested function) Spring
$
92,249
$
30,222
$
54,102
$
62,673
$
25,179
$
70,934
$
74,270
$
72,838
$
94,178
$
49,266
$
29,238
$
43,061
$
47,398
$ 745,608 Summer
$
46,475
$
43,997
$
63,914
$
63,635
$
64,912
$
78,410
$
36,165
$
60,479
$
41,391
$
64,225
$
30,973
$
26,122
$
80,755
$ 701,453 Fall
$
33,947
$
93,029
$
42,642
$
57,410
$
68,875
$
32,994
$
76,548
$
63,324
$
27,235
$
55,410
$
32,145
$
63,391
$
40,446
$ 687,396 er
Total
$
202,582
$
200,000
$
197,649
$
236,641
$
209,053
$
242,016
$
253,368
$
263,577
$
237,311
$
245,790
$
182,871
$
200,000
$
266,693
$ 2,937,551 Location
International
International
Domestic
Domestic
Domestic
Domestic
Domestic
Domestic
Domestic
Domestic
Domestic
Domestic
International Bonus Criteria
Sales Rep Winter Spring Summer Fall Total Location Sales Rep Winter
Ron
$
29,911
Nick
$
32,752
Sally
$
36,991
Susan
$
52,923
Bob
$
50,087
Mark
$
59,678
Swathi
$
66,385
Mike
$
66,936
Rick
$
74,507
Rich
$
76,889
Jill
$
90,515
John
$
67,426
Greg
$
98,094 Spring
$
92,249
$
30,222
$
54,102
$
62,673
$
25,179
$
70,934
$
74,270
$
72,838
$
94,178
$
49,266
$
29,238
$
43,061
$
47,398 Summer
$
46,475
$
43,997
$
63,914
$
63,635
$
64,912
$
78,410
$
36,165
$
60,479
$
41,391
$
64,225
$
30,973
$
26,122
$
80,755 Fall
$
$
$
$
$
$
$
$
$
$
$
$
$ Total
$
$
$
$
$
$
$
$
$
$
$
$
$ Location
International
International
Domestic
Domestic
Domestic
Domestic
Domestic
Domestic
Domestic
Domestic
Domestic
Domestic
International Sales Rep Winter Spring Summer Fall 33,947
93,029
42,642
57,410
68,875
32,994
76,548
63,324
27,235
55,410
32,145
63,391
40,446 Total 202,582
200,000
197,649
236,641
209,053
242,016
253,368
263,577
237,311
245,790
182,871
200,000
266,693 Location Domestic Reps over $200,000
Total Number
Minimum Sales Input Area
Facility costs
Down Payment
# of Pmts per Year
Years
Annual Rate Payment # Summary Calculations
Loan Amount
No. Periods
Monthly Rate
Monthly Payment
Total Interest Paid $ 720,000.00
$ 250,000.00
12
8
5.25% Beginning
Balance Interest Paid Principal Payment Ending Balance
Office 2013 – myitlab:grader – Instructions Excel Project Sales Agent Data 3
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 1 Points
Possible Instructions
View excel-07a.pdf to see the finished product. Download and save the workbook named excel-07a.xlsx.
Open it with Excel 2013. 0.000 Note: Use relative or absolute references in this assignment - NOT mixed references.
2 Click the Sales Data by Agent worksheet and enter a nested function (IF, IF) in cell H8. If the
employee's total sales is less than or equal to the amount in B2 s/he earns a bonus of 0. The bonus for
the remaining employees is indicated in the cells A3:B4. Use absolute references to these cells and
appropriate relative references for the function parameters. Copy the function down the column. 3 In cell B24 select Spring. In cell B25 enter a function to find the column number of the Time Period in
B7:F7 that matches exactly with B24. In cell B24 select Fall to verify this function works properly. 7.000 4 Enter a function in cell B26 that uses the value in cell B25 as an index to display the corresponding sales
for all reps amount from the array B21:F21 (the Row_num is 0). 7.000 5 Enter a nested formula in cell B27 that combines the two formulas from steps 3 and 4. It produces the
same result as the formula in B26. In cell B24 select Total to verify the formulas in B25:B27 are
correct. 8.000 6 Click the Individual Awards worksheet and enter conditions in the Criteria Range for domestic sales
reps that made over $200,000 in total sales. 2.000 7 Perform an advanced filter based on the criteria range. Set the filter to copy the new data into row 22. 7.000 8 In cell J7, enter a database function to calculate the number sales reps that meet the criteria from step 6. 6.000 9 In cell J8, enter a database function to calculate the smallest total sales (use a relative reference to the
name of this field) for a sales rep that meets the criteria from step 6. 6.000 10 Go to the Acquisition worksheet. In cell E2 calculate the loan amount. In cell E3 calculate the total
number of periods. In cell E4 calculate the periodic monthly rate. All three of these formulas must refer
to the loan parameters shown in the input area. 6.000 11 Enter a function in cell E5 to calculate the monthly payment. Refer to the values calculated in the
previous step. Precede the function name with a negative sign to display the result as a positive number. 7.000 Updated: 10/08/2014 1 10.000 Current_Instruction.docx Office 2013 – myitlab:grader – Instructions Excel Project Step Instructions Points
Possible 12 Use the cumulative interest paid function in cell E6 to calculate the total interest paid over the life of the
loan (payments are made at the end of the period). Refer to the values calculated in step 10. Precede the
function name with a negative sign to display the result as a positive number. 7.000 13 In cell A11, start the numbering the payments with 1. Use the fill series command to complete the
numbering through payment 96. In cell B11, create a relative reference to cell E2. 4.000 14 In C11 and D11 enter IPMT and PPMT functions. Refer to the values calculated in step 10 and the
payment number from column A. Precede the function names with a negative sign to display the result
as a positive number. In cell E11, subtract the principle payment amount from the beginning balance
(both from row 11). 12.000 15 In B12, make a reference to E11. Copy C11:E11 to C12:E12. Copy B12:E12 to the end of the table
(ending with payment 96). 11.000 16 Ensure that the worksheets are correctly named and placed in the following order in the workbook:
Sales Data By Agent, IndividualAwards, and Acquisition. Compare your workbook with excel-07a.pdf.
Save the workbook. Close Excel. Submit the workbook to MyITLab.
Total Points Updated: 10/08/2014 2 0.000
100.000 Current_Instruction.docx