finish presentation on powerpoint with the excel information
READ ME BEFORE STARTING Southern Water Corp. Financial Analysis Welcome to your first formal Financial Analysis Exercise for Unit 5! The exercises you will be completing are based off a REAL WORLD Client and will have you focus on using the foundational knowledge you've built in Unit 4 with your Excel Functions, and combining this with the Financial Literacy you've established in Unit 5. It's your chance to prove that you aren't just confident in the topics you've covered - you're able to craft meaningful insights from the data you'll analyse! In this case study, all the data you need to access is located in the Data Repository Table. This contains a mixture of Financial Data and Production Data that you will be using to answer the questions in each of the three (3) tabs, Revenue, Expenses and EBIT. This case study will be challenging, but I'm confident with the skills you've developed across Unit 4 and Unit 5 - you'll be able to tell Management a compelling story regarding what insights you can extract from analysing the Revenues, Expenses and EBIT! Let's get started. What do the tab colours mean? Throughout the Case Study, we've split this into three sections. The green tabs are all tabs you will have to complete. The order of completion is below: 1) Revenue Analysis (Unit 5.4) 2) Expenses Analysis (Unit 5.6) 3) EBIT Analysis (Unit 5.8) What is a Profit Centre and/or Cost Centre? Southern Water Corp sells two types of water product; Hard and Soft Water which is retailed to Residential, Public and Private Customers. These sales (Revenues) are stored in centres known as PROFIT CENTRES. Similarly you can imagine that production of these water products incurs costs. These costs are then stored and allocated to COST CENTRES. You will note that when you explore the dataset in the Data Repository Table - in Column F, "Centre Type", there are two centres. Profit Centres - These are centres which handle all the revenue generating activities. These are then broken down further into Profit Centre Elements of which there are two, one for each water product (Soft Water and Hard Water). Cost Centres - There are centres which handle all cost generating activities. If a business has set costs, they will normally flow through to a cost centre for capture. In most cases, ONLY costs flow through Cost Centres, but depending on company set up - you may have the rare instance where there are revenue-generating activities included in Cost Centres. For Southern Water Corp, we've simplified this structure so revenues stay in Profit Centres and Costs stay in Cost Centres. Refer to the below example to understand how to interpret the information in the Data Repository Table. Definitions Definitions As you create your Value Driver Trees, you are going to rely heavily on the Data Repository Table. In the table below, we've listed what information all the columns contain so you will be aware of how to use this information in your analysis. Account TypeThis column provides information on whether the dataset is referencing Financial Actuals, Financial Budgets, Water Production Actuals or Water Production Budget. Value DriversFor the exercises in 5.4, 5.6 and 5.8 - you will need to create Value Driver Trees. This Column let's you know whether the Value Driver belongs to Revenue or Expenses so you are aware of which cost centres / profit centres you need to pay attention towards. UnitIn the Southern Water Corp. Case Study, there are 3 Units. These are Kootha, Surjek or Jutik. The Unit column indicates, for that row of data, which Unit it applies towards. MonthThis represents the month that data was calculated for. Month (Number)This represents the month in numerical form. Centre TypeCosts / Profits either fall into a Cost Centre or Profit Centre. This column provides the individual with an understanding as to whether the cost centre is a Cost Centre or a Profit Centre. Cost Centre / Profit CentreBased off the Centre Type, this lets the individual know whether the 'Centre' is a Cost Centre or Profit Centre. E.g. Revenue Sales 003 - By itself we don't know if it is a cost centre or profit centre. However, when this is paired with the Centre Type - we know for sure that this is a Profit Centre. Cost Centre / Profit Centre ElementsA Cost Centre/Profit Centre is made up of 'elements' which tie into the Cost Centre. E.g. Revenue Sales 003 is made up of 2 Profit Centres (W-Transact 0211 - Soft, W-Transact 0212 - Hard) Unit of MeasureEvery Row of Data has a Unit of Measure associated with this. E.g. Financial data is measured in $, whilst Water Production data is measure in Giga-Litres. Row DataThis is the unique value that is stored for every row entry. Data Repository Table Data Source ReferenceColumn1Column2Column3Column4Column5Column6Column7Column8Column9 Account TypeValue DriversUnitMonthMonth (Number)Centre TypeCost Centre / Profit CentreCost Centre / Profit Centre ElementsUnit of MeasureRow Data Financial ActualRevenuesKoothaJul-137Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,473,589.05 Financial ActualRevenuesKoothaAug-138Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,419,296.10 Financial ActualRevenuesKoothaSep-139Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,310,673.21 Financial ActualRevenuesKoothaOct-1310Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,301,024.73 Financial ActualRevenuesKoothaNov-1311Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,373,822.86 Financial ActualRevenuesKoothaDec-1312Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,340,623.04 Financial ActualRevenuesKoothaJan-141Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,948,962.55 Financial ActualRevenuesKoothaFeb-142Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,725,161.70 Financial ActualRevenuesKoothaMar-143Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,818,208.62 Financial ActualRevenuesKoothaApr-144Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,328,501.68 Financial ActualRevenuesKoothaMay-145Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,344,117.28 Financial ActualRevenuesKoothaJun-146Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,291,609.13 Financial ActualRevenuesKoothaJul-137Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$1,620,947.95 Financial ActualRevenuesKoothaAug-138Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$1,561,225.71 Financial ActualRevenuesKoothaSep-139Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$1,441,740.53 Financial ActualRevenuesKoothaOct-1310Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$1,431,127.21 Financial ActualRevenuesKoothaNov-1311Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$1,511,205.15 Financial ActualRevenuesKoothaDec-1312Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$1,474,685.34 Financial ActualRevenuesKoothaJan-141Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$2,143,858.81 Financial ActualRevenuesKoothaFeb-142Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$1,897,677.87 Financial ActualRevenuesKoothaMar-143Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$2,000,029.48 Financial ActualRevenuesKoothaApr-144Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$1,461,351.85 Financial ActualRevenuesKoothaMay-145Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$1,478,529.01 Financial ActualRevenuesKoothaJun-146Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$1,420,770.05 Financial ActualRevenuesKoothaJul-137Profit Centre002 Public SalesW-Transact (0211) - Soft$567,331.78 Financial ActualRevenuesKoothaAug-138Profit Centre002 Public SalesW-Transact (0211) - Soft$546,429.00 Financial ActualRevenuesKoothaSep-139Profit Centre002 Public SalesW-Transact (0211) - Soft$504,609.19 Financial ActualRevenuesKoothaOct-1310Profit Centre002 Public SalesW-Transact (0211) - Soft$500,894.52 Financial ActualRevenuesKoothaNov-1311Profit Centre002 Public SalesW-Transact (0211) - Soft$528,921.80 Financial ActualRevenuesKoothaDec-1312Profit Centre002 Public SalesW-Transact (0211) - Soft$516,139.87 Financial ActualRevenuesKoothaJan-141Profit Centre002 Public SalesW-Transact (0211) - Soft$750,350.58 Financial ActualRevenuesKoothaFeb-142Profit Centre002 Public SalesW-Transact (0211) - Soft$664,187.25 Financial ActualRevenuesKoothaMar-143Profit Centre002 Public SalesW-Transact (0211) - Soft$700,010.32 Financial ActualRevenuesKoothaApr-144Profit Centre002 Public SalesW-Transact (0211) - Soft$511,473.15 Financial ActualRevenuesKoothaMay-145Profit Centre002 Public SalesW-Transact (0211) - Soft$517,485.15 Financial ActualRevenuesKoothaJun-146Profit Centre002 Public SalesW-Transact (0211) - Soft$497,269.52 Financial ActualRevenuesKoothaJul-137Profit Centre002 Public SalesW-Transact (0212) - Hard$955,954.05 Financial ActualRevenuesKoothaAug-138Profit Centre002 Public SalesW-Transact (0212) - Hard$920,732.86 Financial ActualRevenuesKoothaSep-139Profit Centre002 Public SalesW-Transact (0212) - Hard$850,266.48 Financial ActualRevenuesKoothaOct-1310Profit Centre002 Public SalesW-Transact (0212) - Hard$844,007.27 Financial ActualRevenuesKoothaNov-1311Profit Centre002 Public SalesW-Transact (0212) - Hard$891,233.24 Financial ActualRevenuesKoothaDec-1312Profit Centre002 Public SalesW-Transact (0212) - Hard$869,695.68 Financial ActualRevenuesKoothaJan-141Profit Centre002 Public SalesW-Transact (0212) - Hard$1,264,340.73 Financial ActualRevenuesKoothaFeb-142Profit Centre002 Public SalesW-Transact (0212) - Hard$1,119,155.52 Financial ActualRevenuesKoothaMar-143Profit Centre002 Public SalesW-Transact (0212) - Hard$1,179,517.39 Financial ActualRevenuesKoothaApr-144Profit Centre002 Public SalesW-Transact (0212) - Hard$861,832.25 Financial ActualRevenuesKoothaMay-145Profit Centre002 Public SalesW-Transact (0212) - Hard$871,962.48 Financial ActualRevenuesKoothaJun-146Profit Centre002 Public SalesW-Transact (0212) - Hard$837,899.14 Financial ActualRevenuesKoothaJul-137Profit Centre003 Residential SalesW-Transact (0211) - Soft$1,296,758.36 Financial ActualRevenuesKoothaAug-138Profit Centre003 Residential SalesW-Transact (0211) - Soft$1,248,980.57 Financial ActualRevenuesKoothaSep-139Profit Centre003 Residential SalesW-Transact (0211) - Soft$1,153,392.42 Financial ActualRevenuesKoothaOct-1310Profit Centre003 Residential SalesW-Transact (0211) - Soft$1,144,901.76 Financial ActualRevenuesKoothaNov-1311Profit Centre003 Residential SalesW-Transact (0211) - Soft$1,208,964.12 Financial ActualRevenuesKoothaDec-1312Profit Centre003 Residential SalesW-Transact (0211) - Soft$1,179,748.27 Financial ActualRevenuesKoothaJan-141Profit Centre003 Residential SalesW-Transact (0211) - Soft$1,715,087.05 Financial ActualRevenuesKoothaFeb-142Profit Centre003 Residential SalesW-Transact (0211) - Soft$1,518,142.29 Financial ActualRevenuesKoothaMar-143Profit Centre003 Residential SalesW-Transact (0211) - Soft$1,600,023.59 Financial ActualRevenuesKoothaApr-144Profit Centre003 Residential SalesW-Transact (0211) - Soft$1,169,081.48 Financial ActualRevenuesKoothaMay-145Profit Centre003 Residential SalesW-Transact (0211) - Soft$1,182,823.21 Financial ActualRevenuesKoothaJun-146Profit Centre003 Residential SalesW-Transact (0211) - Soft$1,136,616.04 Financial ActualRevenuesSurjekJul-137Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$2,406,673.75 Financial ActualRevenuesSurjekAug-138Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$2,028,377.01 Financial ActualRevenuesSurjekSep-139Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$2,241,097.24 Financial ActualRevenuesSurjekOct-1310Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$2,104,393.51 Financial ActualRevenuesSurjekNov-1311Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,921,236.22 Financial ActualRevenuesSurjekDec-1312Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$2,161,522.17 Financial ActualRevenuesSurjekJan-141Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$3,104,730.23 Financial ActualRevenuesSurjekFeb-142Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$2,116,798.71 Financial ActualRevenuesSurjekMar-143Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$2,728,427.89 Financial ActualRevenuesSurjekApr-144Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$2,259,504.87 Financial ActualRevenuesSurjekMay-145Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$2,031,569.24 Financial ActualRevenuesSurjekJun-146Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$2,245,023.23 Financial ActualRevenuesSurjekJul-137Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$4,813,347.49 Financial ActualRevenuesSurjekAug-138Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$4,056,754.01 Financial ActualRevenuesSurjekSep-139Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$4,482,194.48 Financial ActualRevenuesSurjekOct-1310Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$4,208,787.02 Financial ActualRevenuesSurjekNov-1311Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$3,842,472.45 Financial ActualRevenuesSurjekDec-1312Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$4,323,044.34 Financial ActualRevenuesSurjekJan-141Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$6,209,460.45 Financial ActualRevenuesSurjekFeb-142Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$4,633,597.43 Financial ActualRevenuesSurjekMar-143Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$5,456,855.77 Financial ActualRevenuesSurjekApr-144Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$4,519,009.74 Financial ActualRevenuesSurjekMay-145Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$4,063,138.47 Financial ActualRevenuesSurjekJun-146Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$4,490,046.47 Financial ActualRevenuesSurjekJul-137Profit Centre002 Public SalesW-Transact (0211) - Soft$2,117,872.90 Financial ActualRevenuesSurjekAug-138Profit Centre002 Public SalesW-Transact (0211) - Soft$1,784,971.76 Financial ActualRevenuesSurjekSep-139Profit Centre002 Public SalesW-Transact (0211) - Soft$1,972,165.57 Financial ActualRevenuesSurjekOct-1310Profit Centre002 Public SalesW-Transact (0211) - Soft$1,851,866.29 Financial ActualRevenuesSurjekNov-1311Profit Centre002 Public SalesW-Transact (0211) - Soft$1,690,687.88 Financial ActualRevenuesSurjekDec-1312Profit Centre002 Public SalesW-Transact (0211) - Soft$1,902,139.51 Financial ActualRevenuesSurjekJan-141Profit Centre002 Public SalesW-Transact (0211) - Soft$2,732,162.60 Financial ActualRevenuesSurjekFeb-142Profit Centre002 Public SalesW-Transact (0211) - Soft$2,478,782.87 Financial ActualRevenuesSurjekMar-143Profit Centre002 Public SalesW-Transact (0211) - Soft$2,401,016.54 Financial ActualRevenuesSurjekApr-144Profit Centre002 Public SalesW-Transact (0211) - Soft$1,988,364.28 Financial ActualRevenuesSurjekMay-145Profit Centre002 Public SalesW-Transact (0211) - Soft$1,787,780.93 Financial ActualRevenuesSurjekJun-146Profit Centre002 Public SalesW-Transact (0211) - Soft$1,975,620.44 Financial ActualRevenuesSurjekJul-137Profit Centre002 Public SalesW-Transact (0212) - Hard$3,850,677.99 Financial ActualRevenuesSurjekAug-138Profit Centre002 Public SalesW-Transact (0212) - Hard$3,245,403.21 Financial ActualRevenuesSurjekSep-139Profit Centre002 Public SalesW-Transact (0212) - Hard$3,585,755.58 Financial ActualRevenuesSurjekOct-1310Profit Centre002 Public SalesW-Transact (0212) - Hard$3,367,029.62 Financial ActualRevenuesSurjekNov-1311Profit Centre002 Public SalesW-Transact (0212) - Hard$3,073,977.96 Financial ActualRevenuesSurjekDec-1312Profit Centre002 Public SalesW-Transact (0212) - Hard$3,458,435.47 Financial ActualRevenuesSurjekJan-141Profit Centre002 Public SalesW-Transact (0212) - Hard$4,967,568.36 Financial ActualRevenuesSurjekFeb-142Profit Centre002 Public SalesW-Transact (0212) - Hard$4,506,877.94 Financial ActualRevenuesSurjekMar-143Profit Centre002 Public SalesW-Transact (0212) - Hard$4,365,484.62 Financial ActualRevenuesSurjekApr-144Profit Centre002 Public SalesW-Transact (0212) - Hard$4,615,207.79 Financial ActualRevenuesSurjekMay-145Profit Centre002 Public SalesW-Transact (0212) - Hard$3,250,510.78 Financial ActualRevenuesSurjekJun-146Profit Centre002 Public SalesW-Transact (0212) - Hard$3,592,037.17 Financial ActualRevenuesSurjekJul-137Profit Centre003 Residential SalesW-Transact (0211) - Soft$4,139,478.84 Financial ActualRevenuesSurjekAug-138Profit Centre003 Residential SalesW-Transact (0211) - Soft$3,488,808.45 Financial ActualRevenuesSurjekSep-139Profit Centre003 Residential SalesW-Transact (0211) - Soft$3,854,687.25 Financial ActualRevenuesSurjekOct-1310Profit Centre003 Residential SalesW-Transact (0211) - Soft$3,619,556.84 Financial ActualRevenuesSurjekNov-1311Profit Centre003 Residential SalesW-Transact (0211) - Soft$3,304,526.30 Financial ActualRevenuesSurjekDec-1312Profit Centre003 Residential SalesW-Transact (0211) - Soft$3,717,818.13 Financial ActualRevenuesSurjekJan-141Profit Centre003 Residential SalesW-Transact (0211) - Soft$5,340,135.99 Financial ActualRevenuesSurjekFeb-142Profit Centre003 Residential SalesW-Transact (0211) - Soft$4,844,893.79 Financial ActualRevenuesSurjekMar-143Profit Centre003 Residential SalesW-Transact (0211) - Soft$4,692,895.96 Financial ActualRevenuesSurjekApr-144Profit Centre003 Residential SalesW-Transact (0211) - Soft$4,886,348.37 Financial ActualRevenuesSurjekMay-145Profit Centre003 Residential SalesW-Transact (0211) - Soft$3,494,299.08 Financial ActualRevenuesSurjekJun-146Profit Centre003 Residential SalesW-Transact (0211) - Soft$3,861,439.96 Financial ActualRevenuesJutikJul-137Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,766,228.72 Financial ActualRevenuesJutikAug-138Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,951,422.76 Financial ActualRevenuesJutikSep-139Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,699,371.24 Financial ActualRevenuesJutikOct-1310Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,502,189.20 Financial ActualRevenuesJutikNov-1311Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,650,239.51 Financial ActualRevenuesJutikDec-1312Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,406,546.09 Financial ActualRevenuesJutikJan-141Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$2,151,540.20 Financial ActualRevenuesJutikFeb-142Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$2,191,228.23 Financial ActualRevenuesJutikMar-143Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$1,965,526.62 Financial ActualRevenuesJutikApr-144Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$2,084,911.36 Financial ActualRevenuesJutikMay-145Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$2,053,699.35 Financial ActualRevenuesJutikJun-146Profit Centre001 Private Water Hedge SalesW-Transact (0211) - Soft$2,197,266.92 Financial ActualRevenuesJutikJul-137Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$3,532,457.44 Financial ActualRevenuesJutikAug-138Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$3,902,845.52 Financial ActualRevenuesJutikSep-139Profit Centre001 Private Water Hedge SalesW-Transact (0212) - Hard$3,398