Spreadsheet: 694308d1599507312-copy-and-paste-formulas-to-go-from-left-to-right-uploadexcel090720
Sheet: Revenue Analysis

Revenue Analysis - Part I
In Financial Data Analysis, Revenue Analysis speaks primarily to understanding which product(s) generate the most revenues and whether we can identify any aspect of seasonality towards these trends or not.
The importance of this is that we are able to clearly understand which product(s) generate the most revenues and least revenues and this forms the first part of our puzzle.
If a product generates a lot of revenues, but the expenses are greater than the revenues - this isn't a good news story.
However, if the product generates a lot of revenues, and the expenses are well below the revenues - we may have a product which is performing well.
Let's take a look at how Southern Water Corps Revenue Analysis looks like by calculating the Revenues for each of the products and asking ourselves - what does the data tell us?
Let's get started!
Q1. For each of the three Southern Water Corp. Desalination Plants, they produce desalinated water which is then consumed via private, public or residential usage.  Please calculate the monthly revenues for Kootha, Surjek and Jutik and put this in the table below. What trends are you able to pick up?
Note: You will have to rely on the Value Driver Tree you have created earlier to see which cost elements map to the respective Profit Centres.  Please note you will have to reference the account type FINANCIAL ACTUAL in the Data Repository Tab to answer the questions in this Case Study for Revenue Analysis, Expenses Analysis and EBIT Analysis.
Unit | [NAME] | Profit Centre | Profit Centre Element | 2013-07-01 00:00:00 | 2013-08-01 00:00:00 | 2013-09-01 00:00:00 | 2013-10-01 00:00:00 | 2013-11-01 00:00:00 | 2013-12-01 00:00:00 | 2014-01-01 00:00:00 | 2014-02-01 00:00:00 | 2014-03-01 00:00:00 | 2014-04-01 00:00:00 | 2014-05-01 00:00:00 | 2014-06-01 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Total
Kootha | Revenue | 001 Private Water Hedge Sales | W-Transact (0211) - Soft | 2867162.2087479 | 2905157.187601725 | 2676263.627499 | 2491982.7716728 | 2819908.8085938 | 2680307.638373925 | 3885646.640420824 | 3374761.3116714 | 3667690.4272554 | 2736219.54994955 | 2703467.07732885 | 8051520.9365479 |  | A1: Looks like SURJECK reeiving a lot more REV due to HARD water sales
Kootha | Revenue | 001 Private Water Hedge Sales | W-Transact (0212) - Hard | 2867162.2087479 | 3221120.88543938 | 2885932.0209027 | 2877424.35877512 | 3026037.19095832 | 3057907.523045761 | 4329308.475815015 | 3806552.03281353 | 4172261.501252844 | 3040050.256831473 | 2906048.768467075 | 2934884.685778045
Kootha | Revenue | 002 Public Sales | W-Transact (0211) - Soft | 1140053.218129402 | 1099688.359674953 | 993272.7214271398 | 990869.543064324 | 1058055.173230902 | 1064486.866529394 | 1458531.462489466 | 1304198.090668242 | 1367470.157204401 | 1034249.852674433 | 1030209.443343927 | 1002346.164202441
Kootha | Revenue | 002 Public Sales | W-Transact (0212) - Hard | 1907797.506595735 | 1868811.491289612 | 1689904.625337534 | 1681768.884740826 | 1717139.077341934 | 1731998.946401373 | 2518187.435343582 | 2237975.297116084 | 2422728.712251272 | 1735385.427593455 | 1776187.578769495 | 1709314.235664767
Kootha | Revenue | 003 Residential Sales | W-Transact (0211) - Soft | 2594165.10190068 | 2495712.971417204 | 2414396.36283384 | 2324723.03212688 | 2434007.461668552 | 2309711.168448685 | 3550058.676474002 | 3001063.685514049 | 3260368.059480532 | 2282163.959567646 | 2344592.162342584 | 2360865.171449708
Surjek | Revenue | 001 Private Water Hedge Sales | W-Transact (0211) - Soft | 4846559.59019825 | 4098335.738602499 | 4450595.006433625 | 4236354.574980999 | 3854960.48044625 | 4308994.445894999 | 6086513.133090001 | 4207349.120965 | 5361633.639269875 | 4616394.394789251 | 4115959.27011 | 4383407.86145625
Surjek | Revenue | 001 Private Water Hedge Sales | W-Transact (0212) - Hard | 9952558.61024225 | 8002758.635527 | 8828578.46233175 | 8491227.812849998 | 7883600.7154065 | 8812093.582656 | 12408364.817235 | 9282485.7215025 | 11355171.17699525 | 9183531.583467 | 8313587.623467 | 8687790.9051285
Surjek | Revenue | 002 Public Sales | W-Transact (0211) - Soft | 4244217.2849868 | 3615281.81161576 | 3904887.828798 | 3715214.14859056 | 3463543.18236388 | 3802947.52154328 | 5388371.075775601 | 5094890.304831801 | 4898553.94470398 | 3868959.22263972 | 3587361.20771688 | 3937806.67017672
Surjek | Revenue | 002 Public Sales | W-Transact (0212) - Hard | 7724460.055964001 | 6482043.8273384 | 7038121.056349601 | 6723621.4401904 | 6085554.1594932 | 7063508.6080128 | 10181031.35382 | 9108851.004534002 | 8706959.070601 | 8963656.5658536 | 6500371.449844801 | 7039674.449685601
Surjek | Revenue | 003 Residential Sales | W-Transact (0211) - Soft
Jutik | Revenue | 001 Private Water Hedge Sales | W-Transact (0211) - Soft | 3455449.8702535 | 4011344.6280255 | 3492547.769879 | 3050044.959294 | 3271599.821140626 | 2736998.0268015 | 4380320.683000499 | 4377197.504757 | 3915918.677554875 | 4071206.412672 | 4124855.152006875 | 4470779.009754125
Jutik | Revenue | 001 Private Water Hedge Sales | W-Transact (0212) - Hard | 6761476.79068925 | 7900920.475749 | 6857302.822604001 | 5868151.905529 | 6426692.73314 | 5504658.758256 | 8312260.389363 | 8631686.228843998 | 7818078.668796 | 8546885.629184 | 8495743.486593 | 8825542.32593425
Jutik | Revenue | 002 Public Sales | W-Transact (0211) - Soft | 3219382.80428611 | 3564328.31326044 | 2938702.29071551 | 2662359.9695902 | 2936515.38891755 | 2525774.18812488 | 3827796.55476372 | 3796013.65985226 | 3362638.65929343 | 3534408.45463552 | 3645776.38156149 | 3534408.45463552
Jutik | Revenue | 002 Public Sales | W-Transact (0212) - Hard | 5712124.9828202 | 4260894.3644186 | 7666706.093892999 | 6739956.4482978 | 5221621.836096001 | 6640067.747900801 | 7084247.3076648 | 7143053.4210588 | 6036210.8595684 | 6426197.190246401 | 6681587.6254644 | 6895199.3880814
Jutik | Revenue | 003 Residential Sales | W-Transact (0211) - Soft | 6121091.710768193 | 6981074.425933063 | 6013028.001253178 | 5172698.391760899 | 5709749.480128699 | 4895613.051082319 | 7221076.657906018 | 7643731.540931111 | 6618069.634830117 | 7201500.668221438 | 5989219.940977596 | 6294395.308400284
You've now calculated the Revenues for each of the three desalination plants (Kootha, Surjek and Jutik). This has given you a micro-view of the trends, but let's take a look at the macro-revenue trends and view all this information aggregated together. Is there any particular trend(s) that we can pick up with respect to which water product(s) have the highest revenue overall?
Q2) Aggregate the Profit Centre(s) for each Unit (i.e. 001 Private Water Hedge Sales, Public Sales, Residential Sales) in the table below and subsequently plot this out (As a data analyst, it is up to YOU to choose which visual most effectively illustrates the data - as it is time series data, a line chart may be a good consideration).  What trends do you note when comparing Kootha, [NAME] and [NAME]? Is there any plant / product which generates the majority of the revenues?
Unit | [NAME] | Profit Centre |  | 2013-07-01 00:00:00 | 2013-08-01 00:00:00 | 2013-09-01 00:00:00 | 2013-10-01 00:00:00 | 2013-11-01 00:00:00 | 2013-12-01 00:00:00 | 2014-01-01 00:00:00 | 2014-02-01 00:00:00 | 2014-03-01 00:00:00 | 2014-04-01 00:00:00 | 2014-05-01 00:00:00 | 2014-06-01 00:00:00
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Total
Kootha | Revenue | 001 Private Water Hedge Sales
Kootha | Revenue | 002 Public Sales
Kootha | Revenue | 003 Residential Sales |  | 2594165.10190068 | 2495712.971417204 | 2414396.36283384 | 2324723.03212688 | 2434007.461668552 | 2309711.168448685 | 3550058.676474002 | 3001063.685514049 | 3260368.059480532 | 2282163.959567646 | 2344592.162342584 | 2360865.171449708
Surjek | Revenue | 001 Private Water Hedge Sales
Surjek | Revenue | 002 Public Sales
Surjek | Revenue | 003 Residential Sales
Jutik | Revenue | 001 Private Water Hedge Sales
Jutik | Revenue | 002 Public Sales
Jutik | Revenue | 003 Residential Sales
In the previous exercise we've picked up some trends from observing the monthly revenues trended over a twelve month period. Specifically, it's clear that one of the Unit(s) generates the majority of the revenues for Southern Water Corp.  However, it's important for us to understand the contribution each customer segment provides from a % standpoint as well as a dollar stand point.  Let's close out the Revenue Analysis with the below question.   Q3. Calculate the overall % contribution of each customer segment for each of the three (3) units listed below and create a stacked column chart showing the contributions that each customer segment provides per unit. (You can create a stacked column chart showing either % contribution or Overall $ Contribution as per the below example)
 | 001 Private Water Hedge Sales | 002 Public Sales | 003 Residential Sales | Total
Kootha
Surjek
Jutik
 | 001 Private Water Hedge Sales | 002 Public Sales | 003 Residential Sales | Total
Kootha
Surjek
Jutik