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?
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. 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.
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, Surjek and Jutik? Is there any plant / product which generates the majority of the revenue?
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.
In Financial Data Analysis, Expense Analysis speaks primarily to understanding which product(s) are the most expensive to operate and maintain. Think about this as your personal budget; You want to know exactly where your money is being spent. As we mentioned in the last section; we've calculated the revenues and know which of our products generate the most revenues at a Unit Level (Kootha, Surjek, Jutik). It is equally important for us to understand what is our expenses at a unit level. If our expenses outweigh our revenues - this is not a good outcome. If a product generates a lot of revenues, but the expenses are greater than the revenues - this isn't a good news story. Now let's take a look at how Southern Water Corps Expenses look like by calculating the Expenses for each of the Units and asking ourselves - what does the data tell us? Note: You may notice some trends with respect to higher costs for those units which produce a specific type of water.
Q4. For each of the three Southern Water Corp. Desalination Plants, they produce desalinated water which is then consumed via private, public or residential usage. These costs are then aggregated at a Unit Level, separated into Chemical Costs, Facility Costs, Operational Maintenance Costs and Labour Costs. Calculate the monthly expenses for Kootha, Surjek and Jutik and put this in the table below. What trends are you able to pick up from completing the table below?
We've now calculated the Expenses for each of the three desalination plants (Kootha, Surjek and Jutik). This has given us a micro-view of the trends, but let's take a look at the macro-expenses trends and view all this information aggregated together (Kootha + Surjek + Jutik) and then. Are there any particular trend(s) that we can pick up with respect to which water product(s) have the highest expenses overall?
Q5. Aggregate the Cost Centre(s) for each Unit (i.e.Chemical Costs, Facility Costs, Operational Maintenance Costs, Labour Costs) 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. It may be worthwhile using the Total Columns to see the overall aggregate costs by Cost Centre so you can see which cost centre elements are the most expensive...!
In the previous question, we've calculated the expenses at an aggregate level. You would have noticed some particularly high-costs for certain cost elements that you'd be keen to dive into in more detail from a visual stand point.
Q6. Using the Totals for the Year Column (Column R) for each Unit (Kootha, Surjek and Jutik), create a chart which clearly shows the Total Costs for each Cost Element. You will end up with three (3) Column Charts that will let you see which of the Units drive the majority of costs. What trends have you noticed?
We've analysed the expenses data and we have a few questions. The costs for two of our Cost Elements is particularly high - could this potentially be related to the periods of high water production? We can answer this question by using the Water Production Data that is available in the Data Repository Table. However, it is important to note that for the water production data, it has been aggregated. This means we cannot see the separation between Soft / Hard Water Production. This means in the analysis you will complete below, you can only make logical assumptions. As a Data Analyst, you will not always have all the data you need to make a conclusion. In cases like the one below, you will have to draw on logical conclusions from the data available to you and make a conclusion.
Q7. For each of the three units (Kootha, Surjek, Jutik), complete the table below that will show the monthly water production for each Unit as well as the monthly chemical expenditure. Once the table is complete, using a combo-chart, does there appear to be any relationship between the Chemical Expenditure and Water Production?
In Financial Data Analysis, EBIT Analysis, also known as Profitability Analysis, speaks primarily to understanding which product(s) are the most cashflow positive. We're now at the final stage of the puzzle (Woo!); We've got the Revenues. We've got the Expenses. All that is now left is the EBIT and we're done! Once we subtract our Expenses from our Revenues - we can find out how financially sound Southern Water Corp's Units actually are. Let's take one final look at Southern Water Corp's Data for the 2013 - 2014 Period and find out just how profitable the Unit(s) actually are. Additionally, we'll explore how we can use Ratio's to help us show which Unit(s) are most cost-effective from a Revenue to Expenses perspective. EBIT can be simply calculated as Revenues - COGS - Operating Expenses. In the Southern Water Corp. Case Study, we have opted to wrap up our COGS as part of our overall Expenses to simplify the calculation for you. Hence, the EBIT you will calculate will simply be: Revenues - Operating Expenses.
Q8. For each of the three Southern Water Corp. Desalination Plants (Kootha, Surjek and Jutik), calculate the EBIT for each of these below. We have the Total Revenues Per Unit in the Revenues Analysis and the Total Expenses Per Unit in the Expenses Tab. Complete the Table Below and let's find out which of the three Units bring us the most EBIT. Are there any particular trend(s) that you're able to pick up / take note of with respect to the EBIT?
It's pretty clear from our EBIT Analysis, which Unit generates the highest EBIT Margins. However - to close out our analysis, let's also take a look from a Ratio Perspective. That is to say, of Kootha, Surjek and Jutik - which has the highest overall EBIT Margins? The EBIT Margin is calculated as EBIT / Revenue. Would this tell us a different story? Let's close out our EBIT Analysis and find out!
Q9. Of the three units (Kootha, Surjek and Jutik) - Which Unit has the highest overall EBIT Margin? Feel free to trend this out over the 12 Month Period (Jul-13 to June-14) or simply use the Total EBIT / Total Revenue for each Unit. (If you really want - you can use both!) What do you conclude?