Introduction

In the course of my work I create hundreds of cases and Excel files and data sets every year, many of which I give away free of charge here. Every now and again, I create more formal case studies and that is the purpose of this page, to tell you about four such cases I have recently created.

1 Ayala Financial Analysis

File:

You are provided with financial data for Ayala for the years 2006 – 2018: annual and quarterly (last ten quarters)

  • Income Statement
  • Balance Sheet
  • Cash Flow Statement
  • Key Ratios

Using graphical analysis, highlight the performance of the company over the period 2006 – 2018

Using tabular and graphical analysis, highlight the key ratio results for the period 2006 – 2018

Create and review the common size statements

  • Income Statement
  • Balance Sheet
  • Cash Flow Statement

Create and review the growth statements

  • Income Statement
  • Balance Sheet
  • Cash Flow Statement

Create and Review a complete correlation matrix of the annual financial results for the company

Knowledge and Skills Tested

  • Creation of appropriate graphics, including using copying and changing source of data
  • Using hierarchical data and creating Data Validation inputs to tables. Also using VLOOKUP() and/or OFFSET()
  • Create a percentage value in one worksheet based on data in another worksheet
  • Create a rate of change formula in one worksheet based on data in another worksheet
  • Use Power Query, transpose and clean input data. Then using the Data Analysis ToolPak, create the correlation matrix of all appropriate financial data. Use Conditional Formatting to highlight significant, somewhat significant and not significant correlation coefficients

Acquire and Operate a Private Jet

File:

You are provided with a range of data on two separate worksheets, relating to the acquisition and operation of a variety of private jets. The purpose of this case is to create budgets for aircraft or even fleet management using various techniques in budgeting and in Excel.

Firstly, you are provided with a list of key terms for this case: all data in the case are real and are up to date as at 1st May 2019.

Your first task is to create a summary budget for each plane, like this, using a Data Validation selection cell: … see the pdf to see the image

Using Power Query to transpose the input data and create individual budgets in this format: … see the pdf to see the image

Create graphical analyses of the stage two data worksheet.

Still using the stage two data worksheet, create the following: … see the pdf to see the images

Knowledge and Skills Tested

  • Suggest a series of assumptions to allow you to turn raw data into a usable budget
  • Data Validation
  • Power Query
  • Goal Seek
  • INTERCEPT() and SLOPE()
  • Combining data from different worksheets

NOTE: I am currently working on a second version of this case, which will be more advanced in nature than this introductory case.

3 UNCTAD Historical Exchange Rates

File:

This is a comprehensive review of how to turn a number of separate csv files into one single xlsx file that you can use for reference and for more detailed communication and analysis. All tasks are shown in the table that follows.

This file shows the exchange rates from the UNCTAD site for the years 2000 to 2018
The data for each year are downloaded as CSV files and stored in the unctad_exch_rates folder
Data … Get Data … From File … From Folder … select all years and INCLUDE the meta data so that you will have a date column
Rename the Query … 2000 – 2018 or whatever you prefer
Clean the data by tidying the second column from the left that I have now called Base Country
Remove Blanks
ECONOMY
Year – 2000 … YEAR – 2001 … YEAR – 2018
Promote the first row as header … it will say VIS A VIS ECON.
Select columns 1 and 2 now … right click … Unpivot Other Columns
Inspect the Source and Target columns now for errors … there may be many … get rid of them manually
Values column, deselect 1 and – … right click … Remove Errors
Rename all columns, from left to right as Year
Base Country
Target Country
Rate
Close & Load … correct any errors … make any changes in addition to the above that you feel appropriate
Creating a Pivot Table
There are so many countries in the list and so many final rows that creating a Pivot Table needs further thought
You can amend the Query by adding two columns: the first  letter of the Source Country and the first letters of the Target Country I tried the first three then the first two letters … one letter is best!
This will help when you use Slicers to find your countries of interest
Close & Load … correct any errors … make any changes in addition to the above that you feel appropriate
Sharing your Query
To share your Query to save someone else having to go through all of the steps or to ensure that their version of a Query is the same as your own …
Right click your Query in the Queries & Connections pane … Export Connection File…
Save the file to a place you will remember … in my case, I gave my file this name: To use this file, open a new file … Data … Get Data … Using Existing Connection … browse for the .odc file
Query_exch_rates_2000_2018.odc … which is a small change to the name Excel suggested To ensure this works, you must have the source files in the same place on your system as the .odc file says … but it is easy to change the source if you have to .
Open the Query … Click on the Source step … type or paste the new source in the formula bar …

Using Data Validation and the following Database functions, create the following Rate interrogation layout:

  • DGET()
  • DAVERAGE()
  • DMIN()
  • DMAX()
  • DSTDEV() … see the pdf to see the image

Suggest an alternative way to obtain a cross rate to the method shown above.

Suggest the full method used in completing the third section, above.

Knowledge and Skills Tested

  • Power Query
    • Combine separate csv files to create a continuous input table
    • Create initial letter columns to help with managing the pivot table
    • Clean the data
  • Pivot Table
    • Slicers
    • Filters
    • Pivot Chart
    • Grouping
  • Create and use Export Connection File
  • Data Validation
  • DGET()
  • DAVERAGE()
  • DMIN()
  • DMAX()
  • DSTDEV()

4 Mitsubishi Financial and Operational Analysis

File:

Financial and Operational Data
This file contains the following information
Annual, ten years Income Statements
Balance Sheets
Cash Flow Statements
Quarterly, ten quarters Income Statements
Balance Sheets
Cash Flow Statements
Operational Data Product Data
Sales Data
Export Data
Your job is to carry out an appraisal of Mitsubishi Motors
Prepare a Pivot Table that will allow you to summarise the Operational Data
Create an analysis of the financial and operational data to obtain greater understanding of their interrelationships
Assess how all of the above might be used in a financial modelling context

… see the pdf to see the images

Knowledge and Skills Tested

  • Using annual and quarterly data for financial data analysis
  • Graphical analysis
  • Use the operational data set to enhance your understanding of Mitsubishi’s financial results
  • Graphical and mathematical/statistical analysis
  • Excel tables
  • Unique entries
  • Create and use a LOOKUP table
  • Using hierarchical data and creating Data Validation inputs to tables. Also using VLOOKUP() and/or OFFSET()
  • Create a percentage value in one worksheet based on data in another worksheet
  • Create a rate of change formula in one worksheet based on data in another worksheet
  • Create forecasts of financial and operational data using the Forecast Sheet

Download the PDF file that shows all of the above as well as the screenshots that accompany these outlines case_studies_v1_jun2019

 

Duncan Williamson

19th June 2019

%d bloggers like this: