## Case Studies

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.

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.

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