Assignment Task-
Our Health client has requested a summary of the invoices the company have billed to date for their Winter campaign. They want to know how much we have invoiced them to date against each PO value and how much is outstanding.
There are two components to this task
Invoices-
PO VALUES-
Would be very greatful if anyone could help! Thank you
Our Health client has requested a summary of the invoices the company have billed to date for their Winter campaign. They want to know how much we have invoiced them to date against each PO value and how much is outstanding.
There are two components to this task
- Compile the raw data into a table or chart for the client.
- Write a short overview for the client in an email to summarise.
Invoices-
Interview task- Pivot table .xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
2 | Product | Product Name | Campaign | Campaign Name | PO Number | Invoice Number | Invoice | Gross Cost | Net Cost | Service Fee | ASBOF/BASBOF | GCS | Total CTC | Media | ||
3 | 36 | GENERAL Winter | 001 | G_DP_Winter|01691225_NHS | 600205027 | E44150 | 2/21/22 | 17,334.73 | 14,734.53 | 943.01 | 0.00 | 156.78 | 15,834.32 | P | ||
4 | 36 | GENERAL Winter | 001 | G_AV_Winter_MAR22 | 600203579 | E45976 | 3/10/22 | 11,931.00 | 10,141.35 | 649.05 | 10.79 | 108.01 | 10,909.20 | T | ||
5 | 36 | GENERAL Winter | 001 | G_AV_Winter_MAR22 | 600203579 | E45976 | 3/10/22 | 1,091,676.00 | 927,924.60 | 59,387.18 | 987.31 | 9,882.99 | 998,182.08 | T | ||
6 | 36 | GENERAL Winter | 002 | G_AV_Winter_CMP_MAR22 | 600203579 | E45977 | 3/10/22 | 47,724.00 | 40,565.40 | 2,596.19 | 43.16 | 432.05 | 43,636.80 | T | ||
7 | 36 | GENERAL Winter | 004 | G_AV_Winter_MAR22_MABC1 | 600203579 | E49364 | 3/24/22 | 119,673.00 | 101,722.05 | 6,510.22 | 108.23 | 1,083.41 | 109,423.91 | T | ||
8 | 36 | GENERAL Winter | 001 | G_DP_Winter|01691225_NHS | 600205027 | E49320 | 3/24/22 | 243,632.17 | 207,087.35 | 13,253.57 | 0.00 | 2,203.41 | 222,544.33 | P | ||
9 | 36 | GENERAL Winter | 001 | G_CL_Winter | 600205153 | E45657 | 3/8/22 | 925.00 | 925.00 | 0.00 | 0.00 | 9.25 | 934.25 | B | ||
10 | 36 | GENERAL Winter | 001 | G_CL_Winter | 600205153 | E45657 | 3/8/22 | 573,980.00 | 459,184.00 | 29,387.78 | 488.58 | 4,890.60 | 493,950.95 | B | ||
11 | 36 | GENERAL Winter | 001 | G_AV_Winter_ETHNIC_MAR22 | 600203579 | E45985 | 3/10/22 | 41,758.00 | 35,494.30 | 2,271.64 | 37.77 | 378.04 | 38,181.75 | V | ||
12 | 36 | GENERAL Winter | 001 | G_AV_Winter|01691225_NHS | 600203579 | E49439 | 3/24/22 | 231,926.86 | 197,137.83 | 12,616.83 | 209.75 | 2,099.64 | 212,064.05 | W | ||
13 | 36 | GENERAL Winter | 001 | G_AV_Winter_OOHTV | 600203579 | E49127 | 3/24/22 | 40,000.00 | 34,000.00 | 2,176.00 | 36.18 | 362.12 | 36,574.30 | A | ||
14 | 36 | GENERAL Winter | 001 | G_SE_Winter|01691225_NHS | 600205015 | E50017 | 3/28/22 | 125,365.96 | 125,365.96 | 8,023.42 | 133.39 | 1,335.23 | 134,858.00 | O | ||
15 | 36 | GENERAL Winter | 001 | G_SE_Winter|01691225_NHS | 600205015 | E50017 | 3/28/22 | 368.32 | 368.32 | 0.00 | 0.00 | 3.68 | 372.00 | O | ||
16 | 36 | GENERAL Winter | 002 | G_SO_Winter|01691225_NHS | 600205035 | E50018 | 3/28/22 | 90,625.00 | 90,625.00 | 5,800.00 | 96.43 | 965.21 | 97,486.64 | O | ||
17 | 36 | GENERAL Winter | 002 | G_SO_Winter|01691225_NHS | 600205035 | E50018 | 3/28/22 | 90,625.00 | 90,625.00 | 5,800.00 | 96.43 | 965.21 | 97,486.64 | O | ||
18 | 36 | GENERAL Winter | 002 | G_SO_Winter|01691225_NHS | 600205035 | E50018 | 3/28/22 | 69,781.25 | 69,781.25 | 4,466.00 | 74.25 | 743.22 | 75,064.72 | O | ||
19 | 36 | GENERAL Winter | 006 | G_AV_Winter_APR22 | 600203579 | E53974 | 5/10/22 | 1,193.00 | 1,014.05 | 64.90 | 1.08 | 10.80 | 1,090.83 | T | ||
20 | 36 | GENERAL Winter | 006 | G_AV_Winter_APR22 | 600203579 | E53974 | 5/10/22 | 109,167.00 | 92,791.95 | 5,938.69 | 98.73 | 988.29 | 99,817.66 | T | ||
21 | 36 | GENERAL Winter | 008 | G_AV_Winter_APR22_CMP | 600203579 | E53975 | 5/10/22 | 4,772.00 | 4,056.20 | 259.60 | 4.32 | 43.20 | 4,363.32 | T | ||
22 | 36 | GENERAL Winter | 002 | G_AV_Winter_APR22_ETHNIC | 600203579 | E53979 | 5/10/22 | 4,176.00 | 3,549.60 | 227.17 | 3.78 | 37.81 | 3,818.36 | V | ||
23 | 36 | GENERAL Winter | 001 | G_CL_Winter | 600205153 | E54530 | 5/15/22 | 125.00 | 125.00 | 0.00 | 0.00 | 1.25 | 126.25 | B | ||
24 | 36 | GENERAL Winter | 001 | G_DP_Winter|01691225_NHS | 600205027 | E55202 | 5/17/22 | -39.45 | -33.53 | -2.15 | 0.00 | -0.36 | -36.04 | P | ||
25 | 36 | GENERAL Winter | 001 | G_AV_Winter|01691225_NHS | 600203579 | E55932 | 5/18/22 | 54.84 | 54.84 | 0.00 | 0.00 | 0.55 | 55.39 | W | ||
26 | 36 | GENERAL Winter | 001 | G_AV_Winter|01691225_NHS | 600203579 | E55933 | 5/18/22 | 108,786.64 | 92,468.64 | 5,918.00 | 98.39 | 984.85 | 99,469.88 | W | ||
27 | 36 | GENERAL Winter | 001 | G_AV_Winter|01691225_NHS | 600203579 | E55934 | 5/18/22 | 5.31 | 5.31 | 0.00 | 0.00 | 0.05 | 5.36 | W | ||
28 | 36 | GENERAL Winter | 001 | G_SE_Winter|01691225_NHS | 600205015 | E55915 | 5/18/22 | -13,733.26 | -13,733.26 | -878.93 | -14.61 | -146.27 | -14,773.07 | O | ||
29 | 36 | GENERAL Winter | 001 | G_SE_Winter|01691225_NHS | 600205015 | E55915 | 5/18/22 | -39.41 | -39.41 | 0.00 | 0.00 | -0.39 | -39.80 | O | ||
30 | 36 | GENERAL Winter | 001 | G_SE_Winter|01691225_NHS | 600205015 | E55916 | 5/18/22 | 21,868.91 | 21,868.91 | 1,399.61 | 23.27 | 232.92 | 23,524.71 | O | ||
31 | 36 | GENERAL Winter | 001 | G_SE_Winter|01691225_NHS | 600205015 | E55916 | 5/18/22 | 64.36 | 64.36 | 0.00 | 0.00 | 0.64 | 65.00 | O | ||
Invoices |
Cell Formulas | ||
---|---|---|
Range | Formula | |
J10 | J10 | =521800*1.1 |
K10 | K10 | =417440*1.1 |
L10 | L10 | =26716.16*1.1 |
M10 | M10 | =444.16*1.1 |
N10 | N10 | =4446*1.1 |
O10 | O10 | =449046.32*1.1 |
PO VALUES-
Interview task- Pivot table .xlsx | |||||
---|---|---|---|---|---|
C | D | E | |||
2 | Channel | PO Number | Total PO Value | ||
3 | TV, OOH TV and VOD | 600203579 | $1,657,765 | ||
4 | OOH | 600205153 | $450,107 | ||
5 | 600205027 | $240,000 | |||
6 | Social | 600205035 | $298,235 | ||
7 | Search | 600205015 | $300,000 | ||
PO Values |
Would be very greatful if anyone could help! Thank you