Sheet 1: Introduction

	
	

		
Financial Statement Analysis & Security Valuation (Fifth Edition)
	
	

		
Stephen Penman (2012)
	
	

		


	
	

		


	
	

		
Roadmap to Developing Your Own Financial Statement Analysis and Valuation Product
	
	

		


	
	

		


	
	

		
Introduction
	
	

		


	
	

		
This roadmap leads you through the steps to create a valuation tool.  With this tool you will be able to reformulate financial statements in a spreadsheet program, analyze the financial statements using the techniques in the book, forecast pro forma financial statements, and value firms from those pro forma financial statements.  In the end you will have a valuable tool to carry with you into your professional life and to use for your own personal investing.
	
	

		


	
	

		
Use the tool for:
	
	

		


	
	

		
Reformulating financial statements (as in Chapters 8-11)
	
	

		
Analyzing financial statements (as in Chapters 12-13)
	
	

		
Forecasting and valuation (as in Chapters 14-16)
	
	

		
Reverse Engineering (Chapter 7, 19)
	
	

		


	
	

		
You will find that building your own analysis product is very satisfying.  It will help you grasp the concepts behind the analysis, for the concepts come to life as you make them work for you in practice.  And you will discover the points where your understanding is weak.  Off-the-shelf spreadsheet programs that you can use at the press of a button are available but pressing buttons is dangerous if you are not sure about what is going on inside the program.  Better to develop your own model, following sound principles, and so be assured of the integrity of the product.
	
	

		


	
	

		
Once you have followed the roadmap here, think of adding your own bells and whistles.  You can customize the tool for particular industries.  You can add a quality of earnings analysis (as in Chapter 17).  You can utilize spreadsheets features for scenario planning and risk analysis (as in Chapter 18).  And you can add a credit analysis (as in Chapter 19).
	
	

		


	
	

		
Please note that the numbers presented in this spreadshhet might not be exactly same as those on the textbook mainly because of rounding difference.
	



Sheet 2: The Roadmap

	
	

		
Financial Statement Analysis & Security Valuation Fifth Edition)
	
	

		
Stephen Penman 2012
	
	

		


	
	

		
Roadmap to Developing Your Own Financial Statement Analysis and Valuation Product
	
	

		


	
	

		


	
	

		
The roadmap: steps for building the product
	
	

		


	
	

		
Chapter 8 gives the form of the financial statements you should work with and the accounting relations that tie the various parts of the statements together.  So read this chapter before beginning.
	
	

		


	
	

		
Start with reformulation of the financial statements within a spreadsheet, following the design in the book.  Then carry out the financial statement analysis to identify the relevant drivers for forecasting.  Next forecast future financial statements and develop a valuation from the forecasts.
	
	

		


	
	

		
Work with the textbook beside you.  The most relevant part of the text is Chapter 7 to Chapter 16, where financial statement analysis and valuation are covered in detail.  Nike Inc. serves as an example of the application techniques described in the chapters.  This case utilizes Nike's financials for 2000-2010.
	
	

		


	
	

		
The steps:
	
	

		


	
	

		
1.  Download financial statements
	
	

		


	
	

		
2.  Reformulate financial statements
	
	

		
Statements of shareholders' equity
	
	

		
Income statements
	
	

		
Balance sheets
	
	

		
Statements of cash flows
	
	

		


	
	

		
3.  Financial statement analysis
	
	

		
Ratios based on reformulated financial statements
	
	

		
Analysis of profitability
	
	

		
Analysis of growth
	
	

		
Common size and trend analysis
	
	

		


	
	

		
4.  Forecasting and valuation
	
	

		
Valuation essentials
	
	

		
Simple forecasting and valuation
	
	

		
Full-information forecasts and valuation
	
	

		


	
	

		
5.  Bells and whistles
	
	

		


	
	

		
The sections in this primer cover these steps in order.
	



Sheet 3: 1.  Downloading Statements

	
	

		
Financial Statement Analysis & Security Valuation (Fifth Edition)
	
	

		
Stephen Penman 2012
	
	

		


	
	

		
Roadmap to Developing Your Own Financial Statement Analysis and Valuation Product
	
	

		


	
	

		
Step 1: Download Financial Statements
	
	

		


	
	

		
The US Securities and Exchange Commission's Electronic Data Gathering, Analysis and Retrieval (SEC EDGAR) system provides electronic access to companies' financial reports.  Access a company through EDGAR at http://www.sec.gov/edgar/searchedgar/companysearch.html  Several more intelligent interfaces to the EDGAR database have been developed, which enable the downloading of the individual financial statements directly to a spreadsheet program like Microsoft Excel.  Three of these are:
	
	

		


	
	

		
1. EDGARscan developed by PricewaterhouseCoopers at http://edgarscan.pwcglobal.com;
	
	

		
2. 10k Wizard at http://www.10kwizard.com;
	
	

		
3. Edgar Online at http://www.sec.gov/edgar/searchedgar/companysearch.html
	
	

		
 
	
	

		
These sites require subscriptions, however.
	
	

		
All these websites allow searches for companies using either company name or its ticker.  You can then select the reports you need.  Most of the time, you need the annual 10K reports that contain annual financial statements.  You can find the financial statements within the annual 10K reports and will see instructions on how to extract them into Excel files.  As each annual report contains financial statements for the year and one or two years before, you will need to go back to another annual reports of the company to get financial statements for a longer period.  Individual spreadsheet files can then be merged into the format you like.