Answer To: here
Preeta answered on Nov 14 2021
Instructions
Data Analytics Project 2 Instructions
DUE DATE: Tuesday, December 3rd at 11:59PM
NOTE: This is an individual-based project, which means you must work on this and submit a version on your own before the deadline.
Follow the steps below to complete this assignment (worth 150 points total):
1) Save your Excel file with the name "Last Name.First Name - ACCT 307-06 - DA Project 2"
2) For Worksheets 1-12, complete the steps as specified. Each worksheet's tasks can be completed using an application of a combination of topics we have learned in Excel throughout this semester. The instructions for each worksheet are in cells highlighted yellow. The cells you must complete are in cells highlighted green. The color of each worksheet indicates my assessment of the worksheet's difficulty (and its point value) as noted on the Grading Rubric worksheet.
3) For Worksheet 12EC, complete the steps as specified for extra credit. This worksheet is optional.
4) Recall that (as usual) using manual text in any formulas as well as inconsistent formulas should be avoided whenever possible for full credit to be awarded.
When finished:
Submit your final version on Titanium before the deadline. Late work will be harshly penalized but accepted up to 48 hours past the deadline, so it is very highly recommended that you submit before the deadline. Double check that you have named the file properly and followed all steps to the best of your ability. If you have questions before completion, please do not hesitate to let me know!
Grading Rubric
DA Project 2 - Grading Rubric 150
Worksheet % Grade Points Difficulty
Worksheet 1 5.00% 7.50 Easier
Worksheet 2 5.00% 7.50
Worksheet 3 5.00% 7.50
Worksheet 4 5.00% 7.50
Worksheet 5 7.00% 10.50 Moderate
Worksheet 6 7.00% 10.50
Worksheet 7 7.00% 10.50
Worksheet 8 10.00% 15.00 Hard
Worksheet 9 10.00% 15.00
Worksheet 10 12.00% 18.00 Harder
Worksheet 11 12.00% 18.00
Worksheet 12 15.00% 22.50 Hardest
TOTAL (w/ no EC) 100% 150.00
Worksheet 12EC 2.00% 3.00 Optional
TOTAL (w/ EC) 102% 153.00
Worksheet 1
In cell C7 insert formula(s) that will calculate the sum of sales greater than or equal to $25. In cell C4 insert the criteria for the formula(s).
Sales Criteria:
$40.00 >=25
$21.00
$42.00 Formula:
$12.00 $332.00
$33.00
$31.00
$41.00
$48.00
$47.00
$25.00
$25.00
$20.00
Worksheet 2
In the green cells find the problems with the formulas and fix them so that the formulas properly execute.
Brian Eno $4,479.60
Brian Eno $4,479.60
Brian Eno $4,479.60
Brian Eno $4,479.60
Brian Eno $4,479.60
Worksheet 3
In column N, calculate the combination of each student's lowest two quiz grades. In column O, calculate each student's total grade (in points) where the two lowest scores are dropped. In column P, calculate each student's total grade (in %) where the two lowest scores are dropped. Total possible points is 100.
Name Q 1 Q 2 Q 3 Q 4 Q 5 Q 6 Q 7 Q 8 Q 9 Q 10 Q 11 Q 12 Two Lowest Pt. Grade % Grade
Student 1 10 10 7 10 10 7 10 6 9 8 10 9 7 93 93%
Student 2 0 10 8 7 5 7 5 10 9 10 8 8 5 82 82%
Student 3 4 8 8 7 5 10 8 10 10 5 8 5 5 79 79%
Student 4 9 0 10 7 10 10 10 6 3 10 10 7 3 89 89%
Student 5 10 8 10 3 7 5 5 10 5 10 6 5 5 76 76%
Student 6 10 10 10 5 10 4 7 9 10 10 5 10 5 91 91%
Student 7 6 8 5 6 9 10 8 6 4 10 10 5 5 78 78%
Student 8 7 5 5 10 4 10 10 10 5 10 6 10 5 83 83%
Student 9 9 5 8 10 8 7 10 9 7 10 4 7 5 85 85%
Student 10 10 10 10 10 10 10 9 10 6 7 10 10 7 99 99%
Student 11 10 8 9 10 9 6 4 4 10 5 8 4 4 79 79%
Student 12 5 10 10 10 10 5 5 8 10 10 7 8 5 88 88%
Student 13 5 10 5 8 8 8 7 2 5 8 10 4 4 74 74%
Page &P of &N Worksheet name = &A
Advanced Excel for Accounting and Finance - Northwest Accounting Educators Conference 2012. Michael excelisfun Girvin.
Worksheet 4
In the green cells insert formula(s) that will indicate TRUE if all three credit assumptions are met and FALSE otherwise. The assumptions are as follows: (1) sales must be greater than or equal to $250,000; (2) asset value must be greater than or equal to $900,000; (3) credit score must be greater than or equal to 2.5.
Customer Sales Asset Value Credit Score Extend Credit?
Customer 1 $343,916.00 $959,920.00 4.1 TRUE
Customer 2 $275,394.00 $1,040,339.00 1.4 FALSE
Customer 3 $256,331.00 $1,008,433.00 1.7 FALSE
Customer 4 $431,027.00 $959,975.00 1.1 FALSE
Customer 5 $283,174.00 $963,773.00 3.6 TRUE
Customer 6 $296,303.00 $839,012.00 3 FALSE
Customer 7 $264,264.00 $866,385.00 3.3 FALSE
Customer 8 $224,181.00 $799,282.00 1.1 FALSE
Customer 9 $267,021.00 $788,125.00 1.4 FALSE
Customer 10 $209,565.00 $963,794.00 3.1 FALSE
Customer 11 $205,064.00 $766,797.00 2.5 FALSE
Customer 12 $307,681.00 $1,000,711.00 4.8 TRUE
Customer 13 $415,514.00 $1,080,614.00 1.4 FALSE
Customer 14 $198,004.00 $1,014,811.00 2.3 FALSE
Customer 15 $133,078.00 $764,241.00 2.6 FALSE
Customer 16 $228,636.00 $798,686.00 4.8 FALSE
Customer 17 $188,991.00 $1,072,008.00 3.6 FALSE
Customer 18 $263,076.00 $1,006,448.00 4.9 TRUE
Customer 19 $352,011.00 $867,680.00 2.5 FALSE
Customer 20 $361,081.00 $1,035,101.00 1 FALSE
Worksheet 5
In the green cells use formula(s) to determine each sales person's commissions.
ProductLine1: Straight Dollar Commission ProductLine2: Commission Rate x Sales
Date Sales Product ProductLine Sales Person Commissions Product1 $100.00 Product5 0.02
11/14/19 $3,214.21 Product3 ProductLine1 Jan Jelinek 125 Product2 $150.00 Product6 0.03
11/14/19 $2,839.58 Product3 ProductLine1 Richard D. James 125 Product3 $125.00 Product7 0.025
11/14/19 $4,080.47 Product4 ProductLine1 Tim Hecker 110 Product4 $110.00 Product8 0.0275
11/14/19 $4,393.67 Product5 ProductLine2 Christian Fennesz 87.8734
11/14/19 $4,479.60 Product8 ProductLine2 Brian Eno 123.189
11/14/19 $2,654.98 Product5 ProductLine2 Ryuichi Sakamoto 53.0996
11/14/19 $3,994.22 Product7 ProductLine2 Kieran Hebden 99.8555
11/14/19 $4,098.80 Product2 ProductLine1 Michael Sandison 150
11/14/19 $4,734.34 Product6 ProductLine2 Marcus Eoin 142.0302
11/14/19 $3,493.10 Product1 ProductLine1 Sean Booth 100
11/14/19 $3,284.31 Product2 ProductLine1 Rob Brown 150
11/14/19 $4,766.40 Product6 ProductLine2 Markus Popp 142.992
11/14/19 $3,601.61 Product3 ProductLine1 Jan St. Werner 125
11/14/19 $4,272.68 Product7 ProductLine2 Edgar Froese 106.817
11/14/19 $2,142.69 Product3 ProductLine1 Klaus Schulze 125
11/14/19 $4,389.33 Product6 ProductLine2 Peter Baumann 131.6799
11/14/19 $3,876.18 Product4 ProductLine1 Christopher Franke 110
11/14/19 $3,907.71 Product2 ProductLine1 Michael Hoenig 150
11/14/19 $4,150.70 Product4 ProductLine1 Manuel Gottsching 110
11/14/19 $2,773.03 Product5 ProductLine2 Laurel Halo 55.4606
11/14/19 $2,145.51 Product6 ProductLine2 James Holden 64.3653
Worksheet 6
In the green cells use formula(s) to calculate each item's weighted average cost (i.e., Cost of Goods Available for Sale / # Units) without creating a helper column, using Filters, or Pivot Tables.
Date Item Units Cost per Unit Item Weighted Avg. Cost
11/14/19 Item 2 312 $19.20 Item 1 12786.9
11/14/19 Item 3 488 $21.50 Item 2 7060.925
11/14/19 Item 2 432 $20.50 Item 3 5770.55
11/14/19 Item 3 284 $17.80
11/14/19 Item 3 141 $22.20
11/14/19 Item 1 137 $55.40
11/14/19 Item 3 463 $20.30
11/14/19 Item 2 441 $17.20
11/14/19 Item 2 361 $16.10
11/14/19 Item 1 281 $64.00
11/14/19 Item 3 131 $21.40
11/14/19 Item 3 191 $19.60
Page &P of &N Worksheet name = &A
Advanced Excel for Accounting and Finance - Northwest Accounting Educators Conference 2012. Michael excelisfun Girvin.
Worksheet 7
In the green cells calculate total Gross Profit (i.e., Sales - COGS) using the restriction(s) as specified and without creating a helper column, using Filters, or Pivot Tables.
Date Product Region SalesRep Customer Sales COGS No restriction: $ 463,377.77
11/14/19 Product3 Region3 SalesRep2 Customer16 $ 14,046.00 $ 5,337.48 Product10 only: $ 441,182.27
11/14/19 Product7 Region4 SalesRep15 Customer72 $ 2,504.00 $ 1,702.72 Product3 and SalesRep16 only: $ 436,345.70
11/14/19 Product2 Region4 SalesRep18 Customer71 $ 1,505.00 $ 842.80 $ 394,304.11
11/14/19 Product6 Region4 SalesRep14 Customer88 $ 4,232.00 $ 2,793.12
11/14/19 Product3 Region4 SalesRep3 Customer65 $ 5,947.00 $ 3,389.79
11/14/19 Product1 Region8 SalesRep6 Customer100 $ 5,721.00 $ 3,203.76
11/14/19 Product10 Region8 SalesRep16 Customer68 $ 14,744.00 $ 5,307.84
11/14/19 Product7 Region2 SalesRep1 Customer85 $ 4,018.00 $ 2,370.62
11/14/19 Product10 Region5 SalesRep6 Customer6 $ 6,442.00 $ 4,444.98
11/14/19 Product6 Region4 SalesRep7 Customer1 $ 8,160.00 $ 3,508.80
11/14/19 Product7 Region2 SalesRep17 Customer28 $ 7,520.00 $ 4,737.60
11/14/19 Product2 Region4 SalesRep8 Customer16 $ 11,981.00 $ 8,386.70
11/14/19 Product9 Region4 SalesRep1 Customer95 $ 2,468.00 $ 1,209.32
11/14/19 Product7 Region2 SalesRep6 Customer65 $ 14,239.00 $ 7,119.50
11/14/19 Product2 Region8 SalesRep16 Customer69 $ 2,455.00 $ 1,055.65
11/14/19 Product8 Region8 SalesRep10 Customer10 $ 11,538.00 $ 3,576.78
11/14/19 Product10 Region3 SalesRep15 Customer14 $ 4,341.00 $ 1,823.22
11/14/19 Product4 Region6 SalesRep3 Customer74 $ 9,948.00 $ 5,670.36
11/14/19 Product4 Region1 SalesRep17 Customer69 $ 6,525.00 $ 2,675.25
11/14/19 Product2 Region5 SalesRep3 Customer7 $ 7,354.00 $ 4,559.48
11/14/19 Product7 Region1 SalesRep7 Customer49 $ 12,132.00 $ 7,885.80
11/14/19 Product9 Region5 SalesRep1 Customer88 $ 8,853.00 $ 3,983.85
11/14/19 Product2 Region3 SalesRep16 Customer13 $ 14,718.00 $ 5,887.20
11/14/19 Product5 Region8 SalesRep17 Customer52 $ 7,299.00 $ 4,890.33
11/14/19 Product4 Region7 SalesRep15 Customer82 $ 5,125.00 $ 1,588.75
11/14/19 Product10 Region6 SalesRep12 Customer39 $ 9,010.00 $ 2,793.10
11/14/19 Product8 Region6 SalesRep11 Customer65 $ 9,725.00 $ 3,209.25
11/14/19 Product2 Region6 SalesRep15 Customer95 $ 8,443.00 $ 4,643.65
11/14/19 Product3 Region2 SalesRep10 Customer73 $ 12,727.00 $ 4,836.26
11/14/19 Product9 Region4 SalesRep11 Customer68 $ 7,106.00 $ 4,903.14
11/14/19 Product4 Region7 SalesRep18 Customer37 $ 8,479.00 $ 5,850.51
11/14/19 Product7 Region2 SalesRep11 Customer89 $ 13,336.00 $ 6,001.20
11/14/19 Product7 Region7 SalesRep3 Customer71 $ 7,034.00 $ 4,501.76
11/14/19 Product3 Region8 SalesRep11 Customer24 $ 11,587.00 $ 7,647.42
11/14/19 Product5 Region1 SalesRep9 Customer19 $ 14,745.00 $ 8,994.45
11/14/19 Product1 Region7 SalesRep9 Customer54 $ 11,045.00 $ 7,068.80
11/14/19 Product6 Region8 SalesRep2 Customer10 $ 13,767.00 $ 5,644.47
11/14/19 Product1 Region2 SalesRep3 Customer21 $ 3,712.00 $ 2,561.28
11/14/19 Product9 Region6 SalesRep13 Customer47 $ 7,145.00 $ 3,572.50
11/14/19 Product7 Region5 SalesRep17 Customer106 $ 7,843.00 $ 3,607.78
11/14/19 Product6 Region4 SalesRep7 Customer48 $ 8,425.00 $ 2,864.50
11/14/19 Product9 Region5 SalesRep14 Customer44 $ 9,711.00 $ 6,700.59
11/14/19 Product10 Region4 SalesRep15 Customer57 $ 2,477.00 $ 1,114.65
11/14/19 Product4 Region8 SalesRep12 Customer100 $ 14,567.00 $ 7,137.83
11/14/19 Product7 Region8 SalesRep15 Customer83 $ 7,586.00 $ 5,082.62
11/14/19 Product1 Region4 SalesRep6 Customer84 $ 4,679.00 $ 3,228.51
11/14/19 Product10 Region6 SalesRep9 Customer78 $ 8,790.00 $ 3,691.80
11/14/19 Product9 Region6 SalesRep18 Customer98 $ 1,669.00 $ 884.57
11/14/19 Product6 Region6 SalesRep18 Customer23 $ 3,957.00 $ 2,769.90
11/14/19 Product3 Region1 SalesRep17 Customer74 $ 13,490.00 $ 5,530.90
11/14/19 Product10 Region1 SalesRep10 Customer25 $ 2,191.00 $ 1,380.33
11/14/19 Product6 Region5 SalesRep1 Customer57 $ 9,444.00 $ 5,666.40
11/14/19 Product1 Region6 SalesRep14 Customer97 $ 8,260.00 $ 4,130.00
11/14/19 Product1 Region2 SalesRep6 Customer95 $ 4,348.00 $ 1,521.80
11/14/19 Product1 Region6 SalesRep11 Customer57 $ 2,485.00 $ 1,714.65
11/14/19 Product9 Region7 SalesRep12 Customer46 $ 14,228.00 $ 7,256.28
11/14/19 Product1 Region4 SalesRep11 Customer24 $ 8,267.00 $ 2,645.44
11/14/19 Product3 Region8 SalesRep16 Customer59 $ 6,343.00 $ 1,902.90
11/14/19 Product10 Region7 SalesRep13 Customer44 $ 9,381.00 $ 6,097.65
11/14/19 Product8 Region6 SalesRep13 Customer92 $ 11,364.00 $ 7,727.52
11/14/19 Product7 Region2 SalesRep1 Customer85 $ 7,986.00 $ 3,913.14
11/14/19 Product5 Region7 SalesRep12 Customer59 $ 11,126.00 $ 4,450.40
11/14/19 Product7 Region7 SalesRep16 Customer58 $ 10,766.00 $ 5,705.98
11/14/19 Product2 Region7 SalesRep3 Customer48 $ 8,639.00 $ 4,578.67
11/14/19 Product5 Region3 SalesRep15 Customer7 $ 4,992.00 $ 3,494.40
11/14/19 Product7 Region3 SalesRep13 Customer103 $ 12,439.00 $ 6,468.28
11/14/19 Product6 Region6 SalesRep17 Customer66 $ 4,128.00 $ 2,848.32
11/14/19 Product5 Region4 SalesRep4 Customer41 $ 6,464.00 $ 2,908.80
11/14/19 Product10 Region4 SalesRep7 Customer9 $ 3,122.00 $ 1,373.68
11/14/19 Product10 Region2 SalesRep12 Customer27 $ 2,180.00 $ 1,177.20
11/14/19 Product7 Region6 SalesRep12 Customer86 $ 12,241.00 $ 5,630.86
11/14/19 Product7 Region5 SalesRep1 Customer94 $ 11,129.00 $ 7,011.27
11/14/19 Product10 Region2 SalesRep10 Customer4 $ 9,302.00 $ 6,232.34
11/14/19 Product3 Region6 SalesRep16 Customer57 $ 14,856.00 $ 10,102.08
11/14/19 Product6 Region1 SalesRep13 Customer33 $ 3,598.00 $ 2,446.64
11/14/19 Product8 Region3 SalesRep16 Customer35 $ 11,876.00 $ 6,888.08
11/14/19 Product7 Region1 SalesRep17 Customer88 $ 11,821.00 $ 4,373.77
11/14/19 Product6 Region4 SalesRep4 Customer76 $ 11,398.00 $ 3,761.34
11/14/19 Product1 Region8 SalesRep11 Customer70 $ 2,615.00 $ 1,333.65
11/14/19 Product10 Region2 SalesRep11 Customer30 $ 6,544.00 $ 2,944.80
11/14/19 Product8 Region8 SalesRep4 Customer3 $ 3,677.00 $ 1,948.81
11/14/19 Product10 Region1 SalesRep2 Customer11 $ 9,814.00 $ 5,299.56
11/14/19 Product5 Region5 SalesRep1 Customer96 $ 13,823.00 $ 4,838.05
11/14/19 Product4 Region3 SalesRep15 Customer72 $ 6,575.00 $ 2,432.75
11/14/19 Product3 Region5 SalesRep9 Customer17 $ 11,895.00 $ 4,520.10
11/14/19 Product2 Region4 SalesRep3 Customer73 $ 14,707.00 $ 4,853.31
11/14/19 Product5 Region7 SalesRep10 Customer97 $ 10,877.00 $ 3,263.10
11/14/19 Product6 Region7 SalesRep8 Customer19 $ 5,788.00 $ 3,299.16
11/14/19 Product4 Region1 SalesRep16 Customer9 $ 5,141.00 $ 2,724.73
11/14/19 Product1 Region6 SalesRep17 Customer75 $ 3,418.00 $ 1,811.54
11/14/19 Product2 Region4 SalesRep6 Customer43 $ 5,865.00 $ 3,401.70
11/14/19 Product9 Region2 SalesRep13 Customer31 $ 11,198.00 $ 4,927.12
11/14/19 Product5 Region8 SalesRep15 Customer44 $ 6,068.00 $ 2,063.12
11/14/19 Product2 Region6 SalesRep13 Customer92 $ 9,873.00 $ 4,442.85
11/14/19 Product3 Region3 SalesRep1 Customer87 $ 9,409.00 $ 5,174.95
11/14/19 Product1 Region4 SalesRep5 Customer48 $ 2,216.00 $ 1,085.84
11/14/19 Product10 Region5 SalesRep10 Customer30 $ 4,878.00 $ 2,146.32
11/14/19 Product9 Region7 SalesRep7 Customer85 $ 14,973.00 $ 8,834.07
11/14/19 Product2 Region4 SalesRep15 Customer17 $ 13,535.00 $ 5,955.40
11/14/19 Product6 Region4 SalesRep16 Customer59 $ 5,753.00 $ 3,394.27
11/14/19 Product4 Region7 SalesRep10 Customer38 $ 1,781.00 $ 730.21
11/14/19 Product8 Region5 SalesRep4 Customer65 $ 3,842.00 $ 1,959.42
11/14/19 Product8 Region4 SalesRep11 Customer28 $ 7,187.00 $ 2,227.97
11/14/19 Product7 Region6 SalesRep6 Customer58 $ 7,643.00 $ 4,585.80
11/14/19 Product8 Region3 SalesRep5 Customer58 $ 6,451.00 $ 2,451.38
11/14/19 Product9 Region2 SalesRep7 Customer99 $ 5,405.00 $ 2,378.20
11/14/19 Product3 Region4 SalesRep10 Customer56 $ 13,179.00 $ 5,798.76
11/14/19 Product7 Region1 SalesRep5 Customer84 $ 6,199.00 $ 3,223.48
11/14/19 Product2 Region8 SalesRep7 Customer87 $ 2,742.00 $ 932.28
11/14/19 Product8 Region4 SalesRep3 Customer91 $ 3,338.00 $ 1,368.58
11/14/19 Product5 Region6 SalesRep13 Customer47 $ 13,609.00 $ 7,621.04
11/14/19 Product7 Region2 SalesRep17 Customer77 $ 8,812.00 $ 5,199.08
11/14/19 Product4 Region7 SalesRep3 Customer40 $ 2,499.00 $ 1,499.40
11/14/19 Product10 Region4 SalesRep14 Customer81 $ 8,013.00 $ 3,846.24
11/14/19 Product7 Region3 SalesRep1 Customer41 $ 5,777.00 $ 3,408.43
11/14/19 Product7 Region8 SalesRep15 Customer70 $ 6,160.00 $ 3,018.40
Worksheet 8
Complete the loan amortization schedule in the green cells below.
Years Periods per year
30 12
Period PMT Interest Principal Paid Balance Annual Interest Rate Lump Sum 30
0 350,000.00 30
1 ($2,568.18) $2,333.33 ($234.84) $349,765.16 0.08 30
2 ($2,566.45) $2,331.77 ($236.41) $349,528.75 0.08 30
3 ($2,564.72) $2,330.19 ($237.98) $349,290.76 0.08 10,000.00 30
4 ($2,562.97) $2,328.61 ($239.57) $349,051.19 0.08 30
5 ($2,561.21) $2,327.01 ($241.17) $348,810.03 0.08 30
6 ($2,559.44) $2,325.40 ($242.78) $348,567.25 0.08 30
7 ($2,557.66) $2,323.78 ($244.39) $348,322.86 0.08 30
8 ($2,555.87) $2,322.15 ($246.02) $348,076.83 0.08 30
9 ($2,554.06) $2,320.51 ($247.66) $347,829.17 0.08 30
10 ($2,552.25) $2,318.86 ($249.31) $347,579.85 0.08 30
11 ($2,550.42) $2,317.20 ($250.98) $347,328.88 0.08 30
12 ($2,548.58) $2,315.53 ($252.65) $347,076.23 0.08 30
13 ($2,668.72) $2,313.84 ($254.33) $346,821.89 0.085 30
14 ($2,666.76) $2,312.15 ($256.03) $346,565.86 0.085 30
15 ($2,664.79) $2,310.44 ($257.74) $346,308.12 0.085 30
16 ($2,662.81) $2,308.72 ($259.46) $346,048.67 0.085 30
17 ($2,660.81) $2,306.99 ($261.18) $345,787.48 0.085 30
18 ($2,658.81) $2,305.25 ($262.93) $345,524.56 0.085 30
19 ($2,656.78) $2,303.50 ($264.68) $345,259.88 0.085 30
20 ($2,654.75) $2,301.73 ($266.44) $344,993.44 0.085 30
21 ($2,652.70) $2,299.96 ($268.22) $344,725.22 0.085 30
22 ($2,650.64) $2,298.17 ($270.01) $344,455.21 0.085 30
23 ($2,648.56) $2,296.37 ($271.81) $344,183.40 0.085 30
24 ($2,646.47) $2,294.56 ($273.62) $343,909.78 0.085 30
25 ($2,644.37) $2,292.73 ($275.44) $343,634.34 0.085 30
26 ($2,642.25) $2,290.90 ($277.28) $343,357.06 0.085 30
27 ($2,640.12) $2,289.05 ($279.13) $343,077.93 0.085 30
28 ($2,637.97) $2,287.19 ($280.99) $342,796.94 0.085 30
29 ($2,635.81) $2,285.31 ($282.86) $342,514.07 0.085 30
30 ($2,633.64) $2,283.43 ($284.75) $342,229.33 0.085 30
31 ($2,631.45) $2,281.53 ($286.65) $341,942.68 0.085 30
32 ($2,629.24) $2,279.62 ($288.56) $341,654.12 0.085 30
33 ($2,627.02) $2,277.69 ($290.48) $341,363.64 0.085 30
34 ($2,624.79) $2,275.76 ($292.42) $341,071.22 0.085 30
35 ($2,622.54) $2,273.81 ($294.37) $340,776.85 0.085 30
36 ($2,620.28) $2,271.85 ($296.33) $340,480.52 0.085 30
37 ($2,618.00) $2,269.87 ($298.31) $340,182.22 0.085 30
38 ($2,615.71) $2,267.88 ($300.29) $339,881.92 0.085 30
39 ($2,613.40) $2,265.88 ($302.30) $339,579.62 0.085 30
40 ($2,611.07) $2,263.86 ($304.31) $339,275.31 0.085 30
41 ($2,608.73) $2,261.84 ($306.34) $338,968.97 0.085 30
42 ($2,606.38) $2,259.79 ($308.38) $338,660.59 0.085 30
43 ($2,604.01) $2,257.74 ($310.44) $338,350.15 0.085 30
44 ($2,601.62) $2,255.67 ($312.51) $338,037.64 0.085 30
45 ($2,599.22) $2,253.58 ($314.59) $337,723.05 0.085 30
46 ($2,596.80) $2,251.49 ($316.69) $337,406.36 0.085 30
47 ($2,594.36) $2,249.38 ($318.80) $337,087.56 0.085 30
48 ($2,591.91) $2,247.25 ($320.93) $336,766.64 0.085 30
49 ($2,589.44) $2,245.11 ($323.07) $336,443.57 0.085 30
50 ($2,586.96) $2,242.96 ($325.22) $336,118.35 0.085 30
51 ($2,584.46) $2,240.79 ($327.39) $335,790.96 0.085 30
52 ($2,581.94) $2,238.61 ($329.57) $335,461.39 0.085 30
53 ($2,579.41) $2,236.41 ($331.77) $335,129.63 0.085 30
54 ($2,576.86) $2,234.20 ($333.98) $334,795.65 0.085 30
55 ($2,574.29) $2,231.97 ($336.21) $334,459.44 0.085 30
56 ($2,571.70) $2,229.73 ($338.45) $334,121.00 0.085 30
57 ($2,569.10) $2,227.47 ($340.70) $333,780.30 0.085 30
58 ($2,566.48) $2,225.20 ($342.97) $333,437.32 0.085 30
59 ($2,563.84) $2,222.92 ($345.26) $333,092.06 0.085 30
60 ($2,561.19) $2,220.61 ($347.56) $332,744.50 0.085 30
61 ($2,326.60) $2,218.30 ($349.88) $332,394.62 0.075 30
62 ($2,324.15) $2,215.96 ($352.21) $332,042.41 0.075 30
63 ($2,321.69) $2,213.62 ($354.56) $331,687.85 0.075 30
64 ($2,319.21) $2,211.25 ($356.92) $331,330.92 0.075 30
65 ($2,316.71) $2,208.87 ($359.30) $330,971.62 0.075 30
66 ($2,314.20) $2,206.48 ($361.70) $330,609.92 0.075 30
67 ($2,311.67) $2,204.07 ($364.11) $330,245.81 0.075 30
68 ($2,309.13) $2,201.64 ($366.54) $329,879.27 0.075 30
69 ($2,306.56) $2,199.20 ($368.98) $329,510.29 0.075 30
70 ($2,303.98) $2,196.74 ($371.44) $329,138.85 0.075 30
71 ($2,301.39) $2,194.26 ($373.92) $328,764.94 0.075 30
72 ($2,298.77) $2,191.77 ($376.41) $328,388.53 0.075 30
73 ($2,296.14) $2,189.26 ($378.92) $328,009.61 0.075 10,000.00 30
74 ($2,293.49) $2,186.73 ($381.45) $327,628.16 0.075 30
75 ($2,290.82) $2,184.19 ($383.99) $327,244.17 0.075 30
76 ($2,288.14) $2,181.63 ($386.55) $326,857.63 0.075 30
77 ($2,285.44) $2,179.05 ($389.13) $326,468.50 0.075 30
78 ($2,282.72) $2,176.46 ($391.72) $326,076.78 0.075 30
79 ($2,279.98) $2,173.85 ($394.33) $325,682.45 0.075 30
80 ($2,277.22) $2,171.22 ($396.96) $325,285.49 0.075 30
81 ($2,274.44) $2,168.57 ($399.61) $324,885.88 0.075 30
82 ($2,271.65) $2,165.91 ($402.27) $324,483.61 0.075 30
83 ($2,268.84) $2,163.22 ($404.95) $324,078.66 0.075 30
84 ($2,266.01) $2,160.52 ($407.65) $323,671.01 0.075 30
85 ($2,263.15) $2,157.81 ($410.37) $323,260.64 0.075 30
86 ($2,260.29) $2,155.07 ($413.11) $322,847.54 0.075 30
87 ($2,257.40) $2,152.32 ($415.86) $322,431.68 0.075 30
88 ($2,254.49) $2,149.54 ($418.63) $322,013.05 0.075 30
89 ($2,251.56) $2,146.75 ($421.42) $321,591.62 0.075 30
90 ($2,248.62) $2,143.94 ($424.23) $321,167.39 0.075 30
91 ($2,245.65) $2,141.12 ($427.06) $320,740.33 0.075 30
92 ($2,242.66) $2,138.27 ($429.91) $320,310.42 0.075 30
93 ($2,239.66) $2,135.40 ($432.77) $319,877.65 0.075 30
94 ($2,236.63) $2,132.52 ($435.66) $319,441.99 0.075 30
95 ($2,233.58) $2,129.61 ($438.56) $319,003.43 0.075 30
96 ($2,230.52) $2,126.69 ($441.49) $318,561.94 0.075 30
97 ($2,227.43) $2,123.75 ($444.43) $318,117.51 0.075 30
98 ($2,224.32) $2,120.78 ($447.39) $317,670.12 0.075 30
99 ($2,221.20) $2,117.80 ($450.38) $317,219.75 0.075 30
100 ($2,218.05) $2,114.80 ($453.38) $316,766.37 0.075 30
101 ($2,214.88) $2,111.78 ($456.40) $316,309.97 0.075 30
102 ($2,211.69) $2,108.73 ($459.44) $315,850.53 0.075 30
103 ($2,208.47) $2,105.67 ($462.51) $315,388.02 0.075 30
104 ($2,205.24) $2,102.59 ($465.59) $314,922.43 0.075 30
105 ($2,201.98) $2,099.48 ($468.69) $314,453.74 0.075 30
106 ($2,198.71) $2,096.36 ($471.82) $313,981.92 0.075 30
107 ($2,195.41) $2,093.21 ($474.96) $313,506.96 0.075 30
108 ($2,192.09) $2,090.05 ($478.13) $313,028.83 0.075 30
109 ($2,188.74) $2,086.86 ($481.32) $312,547.51 0.075 30
110 ($2,185.38) $2,083.65 ($484.53) $312,062.98 0.075 30
111 ($2,181.99) $2,080.42 ($487.76) $311,575.23 0.075 30
112 ($2,178.58) $2,077.17 ($491.01) $311,084.22 0.075 30
113 ($2,175.15) $2,073.89 ($494.28) $310,589.94 0.075 30
114 ($2,171.69) $2,070.60 ($497.58) $310,092.36 0.075 30
115 ($2,168.21) $2,067.28 ($500.89) $309,591.47 0.075 30
116 ($2,164.71) $2,063.94 ($504.23) $309,087.24 0.075 30
117 ($2,161.18) $2,060.58 ($507.59) $308,579.64 0.075 30
118 ($2,157.63) $2,057.20 ($510.98) $308,068.66 0.075 30
119 ($2,154.06) $2,053.79 ($514.38) $307,554.28 0.075 30
120 ($2,150.46) $2,050.36 ($517.81) $307,036.46 0.075 30
121 ($2,146.84) $2,046.91 ($521.27) $306,515.20 0.075 30
122 ($2,143.20) $2,043.43 ($524.74) $305,990.46 0.075 30
123 ($2,139.53) $2,039.94 ($528.24) $305,462.22 0.075 30
124 ($2,135.84) $2,036.41 ($531.76) $304,930.46 0.075 30
125 ($2,132.12) $2,032.87 ($535.31) $304,395.15 0.075 30
126 ($2,128.38) $2,029.30 ($538.88) $303,856.27 0.075 30
127 ($2,124.61) $2,025.71 ($542.47) $303,313.81 0.075 30
128 ($2,120.81) $2,022.09 ($546.08) $302,767.72 0.075 30
129 ($2,117.00) $2,018.45 ($549.72) $302,218.00 0.075 30
130 ($2,113.15) $2,014.79 ($553.39) $301,664.61 0.075 30
131 ($2,109.28) $2,011.10 ($557.08) $301,107.53 0.075 30
132 ($2,105.39) $2,007.38 ($560.79) $300,546.74 0.075 30
133 ($2,101.47) $2,003.64 ($564.53) $299,982.21 0.075 30
134 ($2,097.52) $1,999.88 ($568.29) $299,413.91 0.075 30
135 ($2,093.55) $1,996.09 ($572.08) $298,841.83 0.075 30
136 ($2,089.55) $1,992.28 ($575.90) $298,265.93 0.075 30
137 ($2,085.52) $1,988.44 ($579.74) $297,686.19 0.075 30
138 ($2,081.47) $1,984.57 ($583.60) $297,102.59 0.075 30
139 ($2,077.38) $1,980.68 ($587.49) $296,515.10 0.075 30
140 ($2,073.28) $1,976.77 ($591.41) $295,923.69 0.075 30
141 ($2,069.14) $1,972.82 ($595.35) $295,328.34 0.075 30
142 ($2,064.98) $1,968.86 ($599.32) $294,729.02 0.075 30
143 ($2,060.79) $1,964.86 ($603.32) $294,125.71 0.075 30
144 ($2,056.57) $1,960.84 ($607.34) $293,518.37 0.075 30
145 ($2,052.32) $1,956.79 ($611.39) $292,906.98 0.075 30
146 ($2,048.05) $1,952.71 ($615.46) $292,291.52 0.075 30
147 ($2,043.74) $1,948.61 ($619.57) $291,671.95 0.075 30
148 ($2,039.41) $1,944.48 ($623.70) $291,048.26 0.075 30
149 ($2,035.05) $1,940.32 ($627.85) $290,420.40 0.075 30
150 ($2,030.66) $1,936.14 ($632.04) $289,788.36 0.075 30
151 ($2,026.24) $1,931.92 ($636.25) $289,152.11 0.075 30
152 ($2,021.79) $1,927.68 ($640.50) $288,511.61 0.075 30
153 ($2,017.32) $1,923.41 ($644.77) $287,866.85 0.075 30
154 ($2,012.81) $1,919.11 ($649.06) $287,217.78 0.075 30
155 ($2,008.27) $1,914.79 ($653.39) $286,564.39 0.075 30
156 ($2,003.70) $1,910.43 ($657.75) $285,906.65 0.075 30
157 ($1,999.10) $1,906.04 ($662.13) $285,244.51 0.075 30
158 ($1,994.47) $1,901.63 ($666.55) $284,577.97 0.075 30
159 ($1,989.81) $1,897.19 ($670.99) $283,906.98 0.075 30
160 ($1,985.12) $1,892.71 ($675.46) $283,231.52 0.075 30
161 ($1,980.40) $1,888.21 ($679.97) $282,551.55 0.075 30
162 ($1,975.64) $1,883.68 ($684.50) $281,867.05 0.075 30
163 ($1,970.86) $1,879.11 ($689.06) $281,177.99 0.075 30
164 ($1,966.04) $1,874.52 ($693.66) $280,484.33 0.075 30
165 ($1,961.19) $1,869.90 ($698.28) $279,786.05 0.075 30
166 ($1,956.30) $1,865.24 ($702.94) $279,083.12 0.075 30
167 ($1,951.39) $1,860.55 ($707.62) $278,375.49 0.075 30
168 ($1,946.44) $1,855.84 ($712.34) $277,663.15 0.075 30
169 ($1,941.46) $1,851.09 ($717.09) $276,946.07 0.075 30
170 ($1,936.45) $1,846.31 ($721.87) $276,224.20 0.075 30
171 ($1,931.40) $1,841.49 ($726.68) $275,497.52 0.075 30
172 ($1,926.32) $1,836.65 ($731.53) $274,765.99 0.075 30
173 ($1,921.20) $1,831.77 ($736.40) $274,029.59 0.075 30
174 ($1,916.05) $1,826.86 ($741.31) $273,288.28 0.075 30
175 ($1,910.87) $1,821.92 ($746.25) $272,542.02 0.075 30
176 ($1,905.65) $1,816.95 ($751.23) $271,790.79 0.075 30
177 ($1,900.40) $1,811.94 ($756.24) $271,034.55 0.075 30
178 ($1,895.11) $1,806.90 ($761.28) $270,273.28 0.075 30
179 ($1,889.79) $1,801.82 ($766.35) $269,506.92 0.075 30
180 ($1,884.43) $1,796.71 ($771.46) $268,735.46 0.075 30
181 ($1,879.04) $1,791.57 ($776.61) $267,958.85 0.075 30
182 ($1,873.61) $1,786.39 ($781.78) $267,177.07 0.075 30
183 ($1,868.14) $1,781.18 ($787.00) $266,390.07 0.075 30
184 ($1,862.64) $1,775.93 ($792.24) $265,597.83 0.075 30
185 ($1,857.10) $1,770.65 ($797.52) $264,800.31 0.075 30
186 ($1,851.52) $1,765.34 ($802.84) $263,997.47 0.075 30
187 ($1,845.91) $1,759.98 ($808.19) $263,189.27 0.075 30
188 ($1,840.26) $1,754.60 ($813.58) $262,375.69 0.075 30
189 ($1,834.57) $1,749.17 ($819.00) $261,556.69 0.075 30
190 ($1,828.84) $1,743.71 ($824.46) $260,732.22 0.075 30
191 ($1,823.08) $1,738.21 ($829.96) $259,902.26 0.075 30
192 ($1,817.27) $1,732.68 ($835.49) $259,066.77 0.075 30
193 ($1,811.43) $1,727.11 ($841.06) $258,225.70 0.075 30
194 ($1,805.55) $1,721.50 ($846.67) $257,379.03 0.075 30
195 ($1,799.63) $1,715.86 ($852.32) $256,526.72 0.075 30
196 ($1,793.67) $1,710.18 ($858.00) $255,668.72 0.075 30
197 ($1,787.67) $1,704.46 ($863.72) $254,805.00 0.075 30
198 ($1,781.63) $1,698.70 ($869.48) $253,935.52 0.075 30
199 ($1,775.55) $1,692.90 ($875.27) $253,060.25 0.075 30
200 ($1,769.43) $1,687.07 ($881.11) $252,179.14 0.075 30
201 ($1,763.27) $1,681.19 ($886.98) $251,292.16 0.075 30
202 ($1,757.07) $1,675.28 ($892.89) $250,399.27 0.075 30
203 ($1,750.83) $1,669.33 ($898.85) $249,500.42 0.075 30
204 ($1,744.54) $1,663.34 ($904.84) $248,595.58 0.075 30
205 ($1,738.22) $1,657.30 ($910.87) $247,684.71 0.075 30
206 ($1,731.85) $1,651.23 ($916.94) $246,767.76 0.075 30
207 ($1,725.44) $1,645.12 ($923.06) $245,844.71 0.075 30
208 ($1,718.98) $1,638.96 ($929.21) $244,915.49 0.075 30
209 ($1,712.48) $1,632.77 ($935.41) $243,980.09 0.075 30
210 ($1,705.94) $1,626.53 ($941.64) $243,038.45 0.075 30
211 ($1,699.36) $1,620.26 ($947.92) $242,090.53 0.075 30
212 ($1,692.73) $1,613.94 ($954.24) $241,136.29 0.075 30
213 ($1,686.06) $1,607.58 ($960.60) $240,175.69 0.075 30
214 ($1,679.34) $1,601.17 ($967.00) $239,208.68 0.075 30
215 ($1,672.58) $1,594.72 ($973.45) $238,235.23 0.075 30
216 ($1,665.78) $1,588.23 ($979.94) $237,255.29 0.075 30
217 ($1,658.92) $1,581.70 ($986.47) $236,268.82 0.075 30
218 ($1,652.03) $1,575.13 ($993.05) $235,275.76 0.075 30
219 ($1,645.08) $1,568.51 ($999.67) $234,276.09 0.075 30
220 ($1,638.09) $1,561.84 ($1,006.34) $233,269.76 0.075 30
221 ($1,631.06) $1,555.13 ($1,013.04) $232,256.71 0.075 30
222 ($1,623.97) $1,548.38 ($1,019.80) $231,236.92 0.075 30
223 ($1,616.84) $1,541.58 ($1,026.60) $230,210.32 0.075 30
224 ($1,609.66) $1,534.74 ($1,033.44) $229,176.88 0.075 30
225 ($1,602.44) $1,527.85 ($1,040.33) $228,136.55 0.075 30
226 ($1,595.16) $1,520.91 ($1,047.27) $227,089.28 0.075 30
227 ($1,587.84) $1,513.93 ($1,054.25) $226,035.04 0.075 30
228 ($1,580.47) $1,506.90 ($1,061.28) $224,973.76 0.075 30
229 ($1,573.05) $1,499.83 ($1,068.35) $223,905.41 0.075 30
230 ($1,565.58) $1,492.70 ($1,075.47) $222,829.94 0.075 30
231 ($1,558.06) $1,485.53 ($1,082.64) $221,747.29 0.075 30
232 ($1,550.49) $1,478.32 ($1,089.86) $220,657.43 0.075 30
233 ($1,542.87) $1,471.05 ($1,097.13) $219,560.31 0.075 30
234 ($1,535.20) $1,463.74 ($1,104.44) $218,455.86 0.075 30
235 ($1,527.48) $1,456.37 ($1,111.80) $217,344.06 0.075 30
236 ($1,519.70) $1,448.96 ($1,119.22) $216,224.85 0.075 30
237 ($1,511.88) $1,441.50 ($1,126.68) $215,098.17 0.075 30
238 ($1,504.00) $1,433.99 ($1,134.19) $213,963.98 0.075 30
239 ($1,496.07) $1,426.43 ($1,141.75) $212,822.23 0.075 30
240 ($1,488.08) $1,418.81 ($1,149.36) $211,672.87 0.075 30
241 ($1,480.05) $1,411.15 ($1,157.02) $210,515.85 0.075 30
242 ($1,471.96) $1,403.44 ($1,164.74) $209,351.11 0.075 30
243 ($1,463.81) $1,395.67 ($1,172.50) $208,178.61 0.075 30
244 ($1,455.62) $1,387.86 ($1,180.32) $206,998.29 0.075 30
245 ($1,447.36) $1,379.99 ($1,188.19) $205,810.10 0.075 30
246 ($1,439.05) $1,372.07 ($1,196.11) $204,613.99 0.075 30
247 ($1,430.69) $1,364.09 ($1,204.08) $203,409.91 0.075 30
248 ($1,422.27) $1,356.07 ($1,212.11) $202,197.80 0.075 30
249 ($1,413.80) $1,347.99 ($1,220.19) $200,977.61 0.075 30
250 ($1,405.26) $1,339.85 ($1,228.33) $199,749.28 0.075 30
251 ($1,396.68) $1,331.66 ($1,236.51) $198,512.77 0.075 30
252 ($1,388.03) $1,323.42 ($1,244.76) $197,268.01 0.075 30
253 ($1,379.33) $1,315.12 ($1,253.06) $196,014.96 0.075 30
254 ($1,370.57) $1,306.77 ($1,261.41) $194,753.55 0.075 30
255 ($1,361.75) $1,298.36 ($1,269.82) $193,483.73 0.075 30
256 ($1,352.87) $1,289.89 ($1,278.28) $192,205.44 0.075 30
257 ($1,343.93) $1,281.37 ($1,286.81) $190,918.64 0.075 30
258 ($1,334.93) $1,272.79 ($1,295.39) $189,623.25 0.075 30
259 ($1,325.87) $1,264.16 ($1,304.02) $188,319.23 0.075 30
260 ($1,316.76) $1,255.46 ($1,312.71) $187,006.52 0.075 30
261 ($1,307.58) $1,246.71 ($1,321.47) $185,685.05 0.075 30
262 ($1,298.34) $1,237.90 ($1,330.28) $184,354.77 0.075 30
263 ($1,289.04) $1,229.03 ($1,339.14) $183,015.63 0.075 30
264 ($1,279.67) $1,220.10 ($1,348.07) $181,667.56 0.075 30
265 ($1,270.25) $1,211.12 ($1,357.06) $180,310.50 0.075 30
266 ($1,260.76) $1,202.07 ($1,366.11) $178,944.39 0.075 30
267 ($1,251.21) $1,192.96 ($1,375.21) $177,569.18 0.075 30
268 ($1,241.59) $1,183.79 ($1,384.38) $176,184.80 0.075 30
269 ($1,231.91) $1,174.57 ($1,393.61) $174,791.19 0.075 30
270 ($1,222.17) $1,165.27 ($1,402.90) $173,388.29 0.075 30
271 ($1,212.36) $1,155.92 ($1,412.25) $171,976.03 0.075 30
272 ($1,202.48) $1,146.51 ($1,421.67) $170,554.36 0.075 30
273 ($1,192.54) $1,137.03 ($1,431.15) $169,123.22 0.075 30
274 ($1,182.53) $1,127.49 ($1,440.69) $167,682.53 0.075 30
275 ($1,172.46) $1,117.88 ($1,450.29) $166,232.24 0.075 30
276 ($1,162.32) $1,108.21 ($1,459.96) $164,772.28 0.075 30
277 ($1,152.11) $1,098.48 ($1,469.69) $163,302.58 0.075 30
278 ($1,141.84) $1,088.68 ($1,479.49) $161,823.09 0.075 30
279 ($1,131.49) $1,078.82 ($1,489.36) $160,333.73 0.075 30
280 ($1,121.08) $1,068.89 ($1,499.28) $158,834.45 0.075 30
281 ($1,110.59) $1,058.90 ($1,509.28) $157,325.17 0.075 30
282 ($1,100.04) $1,048.83 ($1,519.34) $155,805.83 0.075 30
283 ($1,089.42) $1,038.71 ($1,529.47) $154,276.36 0.075 30
284 ($1,078.72) $1,028.51 ($1,539.67) $152,736.69 0.075 30
285 ($1,067.96) $1,018.24 ($1,549.93) $151,186.76 0.075 30
286 ($1,057.12) $1,007.91 ($1,560.26) $149,626.49 0.075 30
287 ($1,046.21) $997.51 ($1,570.67) $148,055.83 0.075 30
288 ($1,035.23) $987.04 ($1,581.14) $146,474.69 0.075 30
289 ($1,024.17) $976.50 ($1,591.68) $144,883.01 0.075 30
290 ($1,013.04) $965.89 ($1,602.29) $143,280.72 0.075 30
291 ($1,001.84) $955.20 ($1,612.97) $141,667.75 0.075 30
292 ($990.56) $944.45 ($1,623.72) $140,044.03 0.075 30
293 ($979.21) $933.63 ($1,634.55) $138,409.48 0.075 30
294 ($967.78) $922.73 ($1,645.45) $136,764.03 0.075 30
295 ($956.27) $911.76 ($1,656.42) $135,107.62 0.075 30
296 ($944.69) $900.72 ($1,667.46) $133,440.16 0.075 30
297 ($933.03) $889.60 ($1,678.57) $131,761.58 0.075 30
298 ($921.30) $878.41 ($1,689.77) $130,071.82 0.075 30
299 ($909.48) $867.15 ($1,701.03) $128,370.79 0.075 30
300 ($897.59) $855.81 ($1,712.37) $126,658.42 0.075 30
301 ($885.61) $844.39 ($1,723.79) $124,934.63 0.075 30
302 ($873.56) $832.90 ($1,735.28) $123,199.35 0.075 30
303 ($861.43) $821.33 ($1,746.85) $121,452.51 0.075 30
304 ($849.21) $809.68 ($1,758.49) $119,694.01 0.075 30
305 ($836.92) $797.96 ($1,770.22) $117,923.80 0.075 30
306 ($824.54) $786.16 ($1,782.02) $116,141.78 0.075 30
307 ($812.08) $774.28 ($1,793.90) $114,347.88 0.075 30
308 ($799.54) $762.32 ($1,805.86) $112,542.03 0.075 30
309 ($786.91) $750.28 ($1,817.90) $110,724.13 0.075 30
310 ($774.20) $738.16 ($1,830.02) $108,894.11 0.075 30
311 ($761.40) $725.96 ($1,842.22) $107,051.90 0.075 30
312 ($748.52) $713.68 ($1,854.50) $105,197.40 0.075 30
313 ($735.56) $701.32 ($1,866.86) $103,330.54 0.075 30
314 ($722.50) $688.87 ($1,879.31) $101,451.24 0.075 30
315 ($709.36) $676.34 ($1,891.83) $99,559.40 0.075 30
316 ($696.13) $663.73 ($1,904.45) $97,654.96 0.075 30
317 ($682.82) $651.03 ($1,917.14) $95,737.81 0.075 30
318 ($669.41) $638.25 ($1,929.92) $93,807.89 0.075 30
319 ($655.92) $625.39 ($1,942.79) $91,865.10 0.075 30
320 ($642.33) $612.43 ($1,955.74) $89,909.36 0.075 30
321 ($628.66) $599.40 ($1,968.78) $87,940.58 0.075 30
322 ($614.89) $586.27 ($1,981.91) $85,958.67 0.075 30
323 ($601.04) $573.06 ($1,995.12) $83,963.55 0.075 30
324 ($587.09) $559.76 ($2,008.42) $81,955.13 0.075 30
325 ($573.04) $546.37 ($2,021.81) $79,933.32 0.075 30
326 ($558.91) $532.89 ($2,035.29) $77,898.04 0.075 30
327 ($544.67) $519.32 ($2,048.86) $75,849.18 0.075 30
328 ($530.35) $505.66 ($2,062.51) $73,786.67 0.075 30
329 ($515.93) $491.91 ($2,076.26) $71,710.40 0.075 30
330 ($501.41) $478.07 ($2,090.11) $69,620.30 0.075 30
331 ($486.80) $464.14 ($2,104.04) $67,516.25 0.075 30
332 ($472.08) $450.11 ($2,118.07) $65,398.19 0.075 30
333 ($457.27) $435.99 ($2,132.19) $63,266.00 0.075 30
334 ($442.37) $421.77 ($2,146.40) $61,119.60 0.075 30
335 ($427.36) $407.46 ($2,160.71) $58,958.88 0.075 30
336 ($412.25) $393.06 ($2,175.12) $56,783.77 0.075 30
337 ($397.04) $378.56 ($2,189.62) $54,594.15 0.075 30
338 ($381.73) $363.96 ($2,204.22) $52,389.94 0.075 30
339 ($366.32) $349.27 ($2,218.91) $50,171.03 0.075 30
340 ($350.80) $334.47 ($2,233.70) $47,937.32 0.075 30
341 ($335.18) $319.58 ($2,248.59) $45,688.73 0.075 30
342 ($319.46) $304.59 ($2,263.58) $43,425.14 0.075 30
343 ($303.63) $289.50 ($2,278.68) $41,146.47 0.075 30
344 ($287.70) $274.31 ($2,293.87) $38,852.60 0.075 30
345 ($271.66) $259.02 ($2,309.16) $36,543.44 0.075 30
346 ($255.52) $243.62 ($2,324.55) $34,218.89 0.075 30
347 ($239.26) $228.13 ($2,340.05) $31,878.84 0.075 30
348 ($222.90) $212.53 ($2,355.65) $29,523.19 0.075 30
349 ($206.43) $196.82 ($2,371.35) $27,151.84 0.075 30
350 ($189.85) $181.01 ($2,387.16) $24,764.67 0.075 30
351 ($173.16) $165.10 ($2,403.08) $22,361.59 0.075 30
352 ($156.36) $149.08 ($2,419.10) $19,942.50 0.075 30
353 ($139.44) $132.95 ($2,435.23) $17,507.27 0.075 30
354 ($122.41) $116.72 ($2,451.46) $15,055.81 0.075 30
355 ($105.27) $100.37 ($2,467.80) $12,588.00 0.075 30
356 ($88.02) $83.92 ($2,484.26) $10,103.75 0.075 30
357 ($70.65) $67.36 ($2,500.82) $7,602.93 0.075 30
358 ($53.16) $50.69 ($2,517.49) $5,085.44 0.075 30
359 ($35.56) $33.90 ($2,534.27) $2,551.17 0.075 30
360 ($17.84) $17.01 ($2,551.17) ($0.00) 0.075 30
Worksheet 9
Create a drop down list in cells B3 and B4. Then add conditional formatting to highlight the Year column, Product row and intersecting cell in the table so that as you change the values in cell B3 and B4 the formatting will change.
Product Product4
Year 2014
2010 2011 2012 2013 2014 2015 2016 2017
Product1 $148,546.00 $145,260.00 $126,372.00 $135,607.00 $129,679.00 $111,892.00 $100,470.00 $111,901.00
Product2 $135,646.00 $96,682.00 $150,431.00 $141,223.00 $142,200.00 $124,103.00 $80,135.00 $151,251.00
Product3 $108,400.00 $94,271.00 $158,998.00 $90,988.00 $155,191.00 $150,893.00 $90,387.00 $156,265.00
Product4 $118,815.00 $91,538.00 $146,520.00 $80,756.00 $148,281.00 $81,459.00 $122,481.00 $148,958.00
Product5 $85,537.00 $144,842.00 $155,701.00 $127,578.00 $141,733.00 $159,043.00 $147,788.00 $111,986.00
Product6 $80,333.00 $119,081.00 $130,797.00 $120,655.00 $131,298.00 $127,321.00 $142,540.00 $156,891.00
Product7 $100,767.00 $147,611.00 $103,388.00 $108,541.00 $104,562.00 $143,196.00 $146,959.00 $89,273.00
Product8 $136,596.00 $93,202.00 $129,286.00 $97,946.00 $139,244.00 $125,708.00 $121,492.00 $98,550.00
Product9 $100,584.00 $119,456.00 $159,582.00 $141,626.00 $155,847.00 $89,087.00 $93,680.00 $136,835.00
Product10 $84,500.00 $95,228.00 $153,251.00 $124,015.00 $84,391.00 $159,122.00 $154,183.00 $98,852.00
Product11 $152,229.00 $116,764.00 $155,102.00 $147,374.00 $127,889.00 $123,911.00 $118,891.00 $148,613.00
Product12 $158,323.00 $132,992.00 $108,734.00 $86,834.00 $128,842.00 $85,926.00 $128,578.00 $80,408.00
Product13 $97,946.00 $94,548.00 $134,937.00 $137,324.00 $87,869.00 $124,531.00 $143,985.00 $84,303.00
Worksheet 10
In the green cells calculate Shipping Expense using the data on the "Region" and "Customer" data tables in columns J and K. If "Warehouse" and "Location" comprise the same letter (i.e., "WarehouseF" and "LocationF"), then Shipping Expense is $100. For every letter in the alphabet further away, add $2.50^X. [EX: If a transaction comprises a sale from "WarehouseA" to "LocationC", then Shipping Expense is equal to $100 + $2.50^2 (where A-->B-->C = 2).
Date Product Region SalesRep Customer Sales COGS Warehouse Location ShippingExp Region Warehouse
11/14/19 Product3 Region3 SalesRep2 Customer16 $ 14,046.00 $ 5,337.48 WarehouseD LocationA $ 102.50 Region1 WarehouseE
11/14/19 Product7 Region4 SalesRep15 Customer72 $ 2,504.00 $ 1,702.72 WarehouseA LocationF $ 102.50 Region2 WarehouseB
11/14/19 Product2 Region4 SalesRep18 Customer71 $ 1,505.00 $ 842.80 WarehouseA LocationA $ 110.00 Region3 WarehouseD
11/14/19 Product6 Region4 SalesRep14 Customer88 $ 4,232.00 $ 2,793.12 WarehouseA LocationB $ 105.00 Region4 WarehouseA
11/14/19 Product3 Region4 SalesRep3 Customer65 $ 5,947.00 $ 3,389.79 WarehouseA LocationB $ 107.50 Region5 WarehouseC
11/14/19 Product1 Region8 SalesRep6 Customer100 $ 5,721.00 $ 3,203.76 WarehouseB LocationB $ 100.00 Region6 WarehouseF
11/14/19 Product10 Region8 SalesRep16 Customer68 $ 14,744.00 $ 5,307.84 WarehouseB LocationB $ ...