Excel Project -1

Gillian Pool and Spa Financial Statement Analysis

The purpose of this project is to make students utilize the concepts and the tools we learn in class and apply them in real world problems. I acknowledge that there are both finance and non-finance students in the same class. Students who are majoring in finance will benefit from this project from a corporate financial management standpoint.

Students who are not majoring in finance will benefit from this project from an investor standpoint. You may certainly use the same tools and techniques for small business financial management if you are planning start a business in the future. Note that most entrepreneurs who fail have the creativity, but they lack the necessary financial and money management skills.

Please read the information below carefully for the details of the project, and dont hesitate to contact me if/when you have questions and/or need clarification.

Project-1 consists of 4 steps:

Step 1: Locate your file on Canvas and download it.

Download the excel file for the company (Gillian Pool and Spa) financial statement information. Make sure you name your excel file as StudentName_ExcelProject1 (e.g.AsligulErkan_ExcelProject1). Also make sure to use excel for calculations. In other words, do not calculate the numbers and type into excel cells. Use MS Excel to make your calculations so that I can track your work and understand what had happened if there is a mistake.

Step 2: The purpose of this step is to help students understand the importance of benchmarking.

Create a new excel worksheet in the same excel file and name it Common Size. Copy the financial statements from your firstworksheet (Financial Statements) to this new worksheet. This will be the basis of your common size calculations. Right next to the financial statement information, construct the common-size financial statements.

Remember common sizing is dividing each balance sheet item by the Total Asset item of the same year, dividing each income statement item by the Total Sales (or Total Revenue) item of the same year. You will do that for each of the five years.

Common size financial statements:

1. Help us compare firms of different sizes.

2. Help us understand the composition of assets, and liabilities, as well as the expenses.

After constructing the common size financial statements of your company, write one paragraph discussion for common-size balance sheet and one paragraph for common-size income statement. As you will see, you can give more precise information about our companies since we are working with percentages now.

Make sure that you are not discussing only the change over time (this you will do in the next step). The purpose is to focus on the composition of assets and liabilities on the common-size balance sheet and how that composition changes over time since we have five years of date. Similarly, you will focus on the expenses as a percentage of sales on the common-size income statement and how those percentages had changed over time.

Step 3: The purpose of this step is to help students understand the importance of trend analysis.

Create a new excel worksheet in the same excel file and name it Common Base-Year. Same as Step 2, copy the financial statements from your first worksheet (Financial Statements) to this new worksheet. This will be the basis of your common base-year calculations. Right next to the financial statement information, construct the common base-year financial statements.

Remember common base-year approach is dividing each financial statement item in any given year by the same item in the base year. For our analysis, we pick 2016 as our base year so that you can compare the firm before and after 2016.

Common base-year financial statements:

1. Help us track down the changes related to a firms assets, liabilities, profitability, etc. over time.

2. Help us understand the composition of assets, and liabilities, as well as the expenses.

After constructing the common base-year financial statements of your company, write one paragraph discussion for common base-year balance sheet and one paragraph for common base-year income statement.

Step 4: The purpose of this step is to help students understand the importance of financial ratio analysis.

Create a new excel worksheet in the same excel workbook and name it Financial Ratios. Copy the financial statements from your second worksheet (named Financial Statements) and paste on the left side of your fourth worksheet (Financial Ratios). This will be the basis of your ratio calculations.

For this step, you will calculate the following ratios for each of the five years:

1. Liquidity ratios: Current ratio, Quick ratio

2. Leverage ratios: D/E ratio, TIE ratio

3. Profitability ratios: Profit margin, ROA, ROE

4. Asset management ratios:

a. Turnover ratios: Total asset turnover, Inventory turnover, Receivables turnover

b. Days sales ratios: Days sales in inventory, Days sales in receivables

5. Market value ratios: Earnings per share (EPS), Price-to-earnings (P/E)

Financial ratios help us:

1. Focus on different aspects of the firm.

2. Understand how well or poorly the firm is doing in these aspects.

3. Track how the firms performance changing over time.

After calculating the financial ratios of your company, write a one paragraph discussion for each of the ratio groups. This will conclude your Excel Project-1 Financial Statement Analysis.

Note 1: I pay attention to the format. Neat work goes a long way!

Note 2: I share with you the videos where I analyze Apple, Inc. financial statements as an example. These videos will help you with formatting your project and discussions in each step. Please note that copying discussions from these videos to yourfile without any change or thought is considered violation of honor code. Furthermore, since you have a different company with different financial statement information, my discussion of Apple, Inc. will not be applicable to Gillian Pool and Spa. Use your judgement, interpretation, and wording in your discussions.

Note 3: A grading rubric is attached to your assignment on Canvas. Overall, you gave 100 points available to you for this project, 15 points for common-size, 15 points for common base-year, 70 points for financial ratio analysis. Grading will be based on format, utilizing MS Excel, calculations, and interpretations.