BMSH2003
Vertical Analysis in MS Excel
Analysis and Interpretation of Financial Statements
Objectives:
At the end of the exercise, the students will be able to:
• create a graphical vertical analysis of the financial statements using the built-in features and
functions of MS Excel.
Equipment:
• Computer installed with MS Excel
Basic Principles:
By just looking at an Income Statement or a Statement of Financial Position, it can be difficult to interpret
all the peso amounts from one accounting period to another or to interpret one company's financial records
compared to another's over a period of time. A useful way to analyze these is by performing both a vertical
analysis and/or a horizontal analysis. This type of analysis allows companies of varying sizes whose peso
amounts are very different to be compared.
A vertical analysis is performed for a specific period such as monthly, quarterly, annually, etc. It is then
compared to similar periods such as the first quarter of 2015, the first quarter of 2016, the first quarter of
2017, etc.
• When performing a vertical analysis of an Income Statement, Net Sales is usually used as the basis
for which all other items are compared. Net Sales is divided by itself making it 100%. All other
items in the Income Statement are divided by the Net Sales.
• When creating a vertical analysis for a Statement of Financial Position, total assets are used as the
basis for analyzing each asset account. Total liabilities and equity are used as the basis for each
liability and stockholder account.
NOTE: Only vertical analysis will be done for this activity.
Procedures:
1. Create a copy of the “SFP” sheet. Right-click the said sheet and select Move or Copy.
2. Rename the new sheet as “SFP – Vertical”.
3. Select column C, right-click Insert. Select column G, right-click Insert.
4. In cells C4 and E4, type ‘Vertical Analysis’.
NOTE: Vertical Analysis (VA) compares an account to the total, the formula is VA = Account/Total
05 Laboratory Exercise 2 *Property of STI
Page 1 of 4
BMSH2003
5. Select C5 and type the formula: =B5/B15. Press F4 to make B15 absolute (=B5/$B$15). The total
is made absolute so that when the formula is filled, the cell for the total remains constant.
6. Format cell C5. Click the Percent Icon on the Home Ribbon (Numbers Group).
7. Position the mouse on the Fill Handle. Click and drag to C8.
8. Select C9 and double click the AutoSum Icon. Click the Percent Icon.
9. Do the same procedure to Non-Current Asset, Current Liabilities, and Owner’s Equity. Remember
the amounts that need to be deducted under Owner’s Equity classification.
10. The format of the vertical analysis for the Statement of Financial Position should look like this:
CHAN ACCOUNTING FIRM
Statement of Financial Position
December 31, 20CY
Vertical Vertical
Current Assets Current Liabilities
Analysis Analysis
Cash ₱1,209,000.00 Accounts Payable ₱ 50,000.00
Accounts Receivable 150,000.00 Notes Payable 200,000.00
Office Supplies 30,000.00 Unearned Revenue 70,000.00
Prepaid Rent 150,000.00 Total ₱ 320,000.00
Total ₱ 1,539,000.00
Owner's Equity
Non-current Assets C. Chan, Capital ₱ 1,000,000.00
Office Equipment ₱60,000.00 C. Chan, Drawing 100,000.00
Total ₱60,000.00 Professional Fees 495,000.00
Salaries Expense 30,000.00
Total Assets ₱1,599,000.00 100% Utilities Expense 16,000.00
Representation Expense 50,000.00
Permits and Licenses Expense 20,000.00
Total ₱ 1,279,000.00
Total Liabilities & Equity 1,599,000.00 100%
Figure 1. Vertical analysis - SFP
11. Afterwards, create a graphical analysis of the Total Assets and Total Liabilities and Equity.
12. Select the account names and percentages for each account. Press Ctrl to limit selection.
Figure 2. Selecting account names and percentages
13. After selecting the data, insert charts and choose Pie or Doughnut Chart.
05 Laboratory Exercise 2 *Property of STI
Page 2 of 4
BMSH2003
Figure 3. Graphical presentation
14. Modify the desired presentation of the chart.
15. Repeat procedures 11-14 to Total Liabilities and Equity.
16. Create a copy of the “IS” sheet by right-clicking the sheet and select Move or Copy.
17. Rename the new sheet as “IS – Vertical”.
18. In cell C4, type ‘Percentage’.
19. Select C5 and type the formula: =B5/B5. Press F4 to make B5 absolute =B5/$B$5).
20. Format the cell C5. Click the Percent Icon on the Home Ribbon (Numbers Group).
21. Position the mouse on the Fill Handle. Click and drag to C9.
22. Select C10, double-click the AutoSum Icon. Click the Percent Icon.
23. The format of the vertical analysis for the Income Statement should look like this:
CHAN ACCOUNTING FIRM
Income Statement
For the Year Ended December 31, 20CY
Percentage
Professional Fees ₱ 495,000.00 100%
Salaries Expense (30,000.00)
Utilities Expense (16,000.00)
Representation Expense (50,000.00)
Permits and Licenses Expense (20,000.00)
Net Income/(Loss) ₱ 379,000.00
Figure 4. Vertical analysis - IS
05 Laboratory Exercise 2 *Property of STI
Page 3 of 4
BMSH2003
24. Afterwards, create a graphical analysis of the Income Statement.
25. Select the account names and percentages for each account. Press Ctrl to limit selection.
26. After selecting the data, insert charts and choose Pie or Doughnut Chart just like in the statement
of financial position.
27. Modify the desired presentation of the chart.
28. Save the file.
Rubric for scoring:
CRITERIA PERFORMANCE INDICATORS POINTS
Neatness and The spreadsheet has exceptional formatting, and the information is well-
20
Organization organized.
The spreadsheet formulas are well-developed and will correctly determine the
Formulas 30
needed information.
Titles, Labels, and
The spreadsheet contains clearly labeled rows and columns. 10
Headings
The spreadsheet meets all the necessary requirements to interpret the result of
Content 40
the operation.
TOTAL 100
05 Laboratory Exercise 2 *Property of STI
Page 4 of 4
