Budget Spreadsheet 32453

Partial file snippet.

Sheet 1: 1 - Control Menu

	
	
	
	
	
	
	
		Financial Analysis & Forecasting
		

		

		Prepared by Matt H. Evans, CPA, CMA, CFM
		
	
		

		

		

		
	
		Purpose of Spreadsheet:
		

		

		Revised:
		11/22/2002
		

		
		Home - Active Worksheet
		active
		
	General Input
	Wksh2
	
	
		

		

		

		

		

		

		
		Balance Sheet
		Wksh3
		
	Income Statement
	Wksh4
	
	
		To illustrate concepts related to financial analysis and forecasting. 
		

		

		

		

		

		
		Cash Flow Statement
		Wksh5
		
	Key Financials
	Wksh6
	
	
		The financial analysis uses a combination of ratios and industry averages to 
		

		

		

		

		

		
		Ratio Analysis
		Wksh7
		
	Benchmark Analysis
	Wksh8
	
	
		evaluate the financial performance of the company. Trend line graphs are also
		

		

		

		

		

		
		Horizontal Analysis
		Wksh9
		
	Vertical Analysis
	Wksh10
	
	
		generated, comparing the company's performance with the industry averages.
		

		

		

		

		

		
		Pro Forma Financials - Simple Projection Model
		Wksh11
		
	Pro Forma Financials - Linear Trend Model
	Wksh12
	
	
		Finally, the historical information is used to prepare a set of pro forma 
		

		

		

		

		

		
		Pro Forma Financials - Exponential Smoothing
		Wksh13
		
	Scenario Analysis
	Wksh14
	
	
		financial statements using both linear and non-linear functions.
		

		

		

		

		

		
		Budget Analysis
		Wksh15
		
	Final Budgets
	Wksh16
	
	
		

		

		

		

		

		

		

		

	
	
		Required Inputs:
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		You will need to collect financial statements for several reporting periods. If you
		

		

		

		

		

		

		

	
	
		want to benchmark the performance against the industry, then you will also need
		

		

		

		

		

		

		

	
	
		to collect industry averages. The spreadsheet is setup to capture five reporting
		

		

		

		

		

		

		

	
	
		periods (annual, quarterly, monthly). All input fields are highlighted in yellow.
		

		

		

		

		

		

		

	
	
		For best results, SEC Filings are suggested since these reports provide more 
		

		

		

		

		

		

		

	
	
		detail than published financial statements.
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Note: A small red triangle in the upper right corner of a cell indicates that a comment has 
		

		

		

		

		

		

		

	
	
		been inserted. Point your mouse over the cell and the comment will appear.
		

		

		

		

		
		Comment boxes are used to describe accounts, ratios, and other information used in this spreadsheet! Whenever you see a small red triangle, point your mouse over this cell for additional information.
		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		If a cell appears in red, this indicates a warning concerning a calculation.
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Worksheets:
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		This spreadsheet consists of the following worksheets, divided into three sections:
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		A) Input Worksheets for financial analysis using historical data:
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		Worksheet
		Title
		Purpose
		
	
		

		2
		General Input
		Enter general information here - used on several worksheets.
		

		

		

		

	
	
		

		3
		Balance Sheet
		Enter comparative balances sheets for up to five periods.
		

		

		

		

	
	
		

		4
		Income Statement
		Enter comparative income statements for up to five periods.
		

		

		

		

	
	
		

		5
		Cash Flow Statement
		Enter comparative cash flow statements for up to five periods.
		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		

		Caution: If you enter less than five years of historical information, certain worksheet 
		

		

		

		

		

	
	
		

		

		formulas may have to be revised.
		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		B) Output Worksheets for evaluating financial performance:
		

		

		

		

		

		

		

	
	
		

		6
		Key Financial Data
		Calculates key financial information for further analysis.
		

		

		

		

	
	
		

		7
		Ratio Analysis
		Calculates a series of ratios for further analysis.
		

		

		

		

	
	
		

		8
		Benchmark Analysis
		Compare ratio analysis to industry averages.
		

		

		

		

	
	
		

		9
		Horizontal Analysis
		Horizontal analysis with corresponding trend lines.
		

		

		

		

	
	
		

		10
		Vertical Analysis
		Common size financials in percentages and graphs.
		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		C) Pro Forma / Forecasted Financials for Budgeting:
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		11
		Pro Forma - Simple
		Set of pro forma financials using simple assumptions
		

		

		

		

	
	
		

		12
		Pro Forma - Regression
		Set of pro forma financials using linear trending
		

		

		

		

	
	
		

		13
		Pro Forma - Exponential
		Set of pro forma financials using exponential smoothing
		

		

		

		

	
	
		

		14
		Scenario Analysis
		Example of Scenario Analysis and Goal Seek Analysis
		

		

		

		

	
	
		

		15
		Budget Analysis
		Preliminary budget analysis
		

		

		

		

	
	
		

		16
		Final Budgets
		Set of budgets per various assumptions and forecasts.
		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		

		Note: Some additional worksheets (Answer Reports 1 & 2) may appear in the spreadsheet
		

		

		

		

		

		

	
	
		

		due to the running of Solver.
		

		

		

		

		

		

	
	
		Macros:
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		No macros have been used in this spreadsheet to give everyone some assurance that no viruses
		

		

		

		

		

		

		

	
	
		are contained in the spreadsheet. However, you are free to add your own macros to save time.
		

		

		

		

		

		

		

	
	
		Tools > Macro > Record New Macro
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Excel Functions:
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		This spreadsheet uses certain financial functions (such as =TREND) which might not be
		

		

		

		

		

		

		

	
	
		found in your version of Microsoft Excel. To take full advantage of financial and statistical
		

		

		

		

		

		

		

	
	
		functions, you should install the Add On package titled: Analysis TookPak. Go to the main
		

		

		

		

		

		

		

	
	
		tool bar, select Tools => Add-Ins => check the Analysis TookPak option, insert your 
		

		

		

		

		

		

		

	
	
		Excel CD and install the Analysis ToolPak. Also, you might want to install the Solver
		

		

		

		

		

		

		

	
	
		Add-in since this is useful for solving special forecasting issues (such as finding the 
		

		

		

		

		

		

		

	
	
		optimal exponential factor).
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Compatibility:
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		This spreadsheet was created with Microsoft Excel 2000. Older versions of Excel (such as 97) 
		

		

		

		

		

		

		

	
	
		may not be compatible with this spreadsheet. 
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

	
	
		Corrections:
		

		

		

		

		

		

		

	
	
		With any “attempt” to build an Excel Model, I can easily make some mistakes.
		

		

		

		

		

		

		

	
	
		So if you have suggestions to make the model better, drop me an email 
		

		

		

		

		

		

		

	
	
		and I’ll be glad to improve the financial model. My email address is:
		

		

		

		

		matt@exinfm.com
		

		

	



Sheet 2: 2 - General Input

	
	
	
	
	
	
	
	
		

		General Input Panel
		

		

		

		

		

		

		
		Return to 1st Worksheet
		Home
		
	General Input
	active
	
	
		

		The following general information should be entered:
		

		

		

		

		

		

		
		Balance Sheet
		Wksh3
		
	Income Statement
	Wksh4
	
	
		

		Note: Sample data has been entered in the input cells to help you get started.
		

		

		

		

		

		

		
		Cash Flow Statement
		Wksh5
		
	Key Financials
	Wksh6
	
	
		

		

		

		

		

		

		

		

		
		Ratio Analysis
		Wksh7
		
	Benchmark Analysis
	Wksh8
	
	
		2-1
		Name of Company =>
		

		X Y Z Corporation USA
		

		

		
		Horizontal Analysis
		Wksh9
		
	Vertical Analysis
	Wksh10
	
	
		

		

		

		

		

		

		

		

		
		Pro Forma Financials - Simple Projection Model
		Wksh11
		
	Pro Forma Financials - Linear Trend Model
	Wksh12
	
	
		2-2
		Reporting Periods =>
		

		Annual
		(Annual, Semi-annual, Quarterly or Monthly)
		

		

		

		
		Pro Forma Financials - Exponential Smoothing
		Wksh13
		
	Scenario Analysis
	Wksh14
	
	
		

		

		

		

		

		

		

		

		
		Budget Analysis
		Wksh15
		
	Final Budgets
	Wksh16
	
	
		2-3
		Number of Days in Reporting Period are
		

		

		365
		

		

		

		

		

	
	
		

		

		

		

		 
		

		

		

		

		

	
	
		

		What reporting periods will be entered?
		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		2-4
		Most Current Period
		

		2000
		(1999, July 1998, 6/30/97, etc.)
		

		

		

		

		

	
	
		2-5
		Previous Period
		

		1999
		(1999, July 1998, 6/30/97, etc.)
		

		

		

		

		

	
	
		2-6
		2nd Previous Period
		

		1998
		(1999, July 1998, 6/30/97, etc.)
		

		

		

		

		

	
	
		2-7
		3rd Previous Period
		

		1997
		(1999, July 1998, 6/30/97, etc.)
		

		

		

		

		

	
	
		2-8
		4th Previous Period
		

		1996
		(1999, July 1998, 6/30/97, etc.)
		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		2-9
		Number of historical periods to be analyzed 
		

		

		

		5
		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		

		How are the amounts expressed in the financial statements?
		

		

		

		

		

		

		

		

	
	
		

		(such as: in millions of dollars, thousands of Canadian dollars, etc.)
		

		

		

		

		

		

		

		

	
	
		2-10
		millions of dollars
		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

	
	
		

		

		 
		

		

		

		

		

		

		

	



Sheet 3: 3 - Balance Sheet

	
	
	
	
	
		

		Home
		Balance Sheet for
		

		

		

		

		

		

		

		

		

		 
		

		 
	
	
		>>
		>
		X Y Z Corporation USA
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		<<
		<
		 
		millions of dollars
		

		

		

		

		

		

		

	
	
		

		End
		

		Annual
		Annual
		Annual
		Annual
		Annual
		

		

		

		

		

		

		

	
	
		

		

		

		Period
		Period
		Period
		Period
		Period
		

		

		

		

		

		

		

	
	
		

		Ref
		Description
		1996
		1997
		1998
		1999
		2000
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Current Assets
		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		3-1
		
		All cash on hand and any deposits that can be converted into cash. Restricted Cash should be Other Current Assets

		Cash and Cash Equivalents
		990 
		950 
		901 
		998 
		870 
		

		

		

		

		

		

		

	
	
		3-2
		
		Can be held long-term, but is held for the purpose of earning a return on idle cash.

		Short Term Marketable Securities
		10 
		15 
		12 
		6 
		11 
		

		

		

		

		

		

		

	
	
		3-3
		
		Receivables from sales of products on account. Notes Receivalbe should be reported in Other Current Assets for accurate ratio analysis.
		Accounts Receivable
		1,020 
		1,550 
		1,830 
		2,250 
		3,040 
		

		

		

		

		

		

		

	
	
		3-4
		
		Assets held for sale in ordinary business. All cost such as storage, handling, etc are part of inventory. There forms of inventory are Raw Materials, Work in Progress, and Finished Inventory. 
		Inventory 
		1,005 
		1,360 
		1,650 
		1,900 
		2,060 
		

		

		

		

		

		

		

	
	
		3-5
		
		All other current assets not otherwise categorized, such as prepaid items.

		Other Current Assets
		870 
		1,150 
		1,370 
		1,650 
		1,530 
		

		

		

		

		

		

		

	
	
		3-6
		Total Current Assets
		3,895 
		5,025 
		5,763 
		6,804 
		7,511 
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		NonCurrent Assets
		3-7
		
		Noncurrent tangible assets used in business operations, such as equipment, vehicles, machinery, land, etc.

		Fixed Assets
		14,006 
		17,605 
		21,826 
		26,950 
		28,100 
		

		

		

		

		

		

		

	
	
		3-8
		
		Cumulative depreciation taken on fixed assets.
		Accumulated Depreciation
		(1,280)
		(1,700)
		(2,100)
		(2,550)
		(3,010)
		

		

		

		

		

		

		

	
	
		3-9
		Net Fixed Assets
		12,726 
		15,905 
		19,726 
		24,400 
		25,090 
		

		

		

		

		

		

		

	
	
		3-10
		
		All longterm investments at cost.
		Longterm Investments 
		360 
		320 
		120 
		590 
		905 
		

		

		

		

		

		

		

	
	
		3-11
		
		Investments under the Equity Method in other companies
		Investments in Other Companies
		65 
		0 
		0 
		250 
		412 
		

		

		

		

		

		

		

	
	
		3-12
		
		All other non current assets, such as goodwill, trademarks, etc.
		Intangibles and Other Assets
		100 
		110 
		105 
		135 
		195 
		

		

		

		

		

		

		

	
	
		

		

		Total Non Current Assets
		13,251 
		16,335 
		19,951 
		25,375 
		26,602 
		

		

		

		

		

		

		

	
	
		

		3-13
		Total Assets
		17,146 
		21,360 
		25,714 
		32,179 
		34,113 
		

		

		

		

		

		

		

	
	
		

		

		

		 
		 
		 
		 
		 
		

		

		

		

		

		

		

	
	
		Currrent Liab
		3-14
		
		Payables related to normal trade transactions with suppliers, vendors, etc. Does not include notes payable.
		Accounts Payable
		2,050 
		3,150 
		3,290 
		3,870 
		4,800 
		

		

		

		

		

		

		

	
	
		3-15
		
		Short term loans, notes payable, commercial paper, etc.
		Short Term Borrowings
		1,200 
		1,830 
		2,580 
		3,100 
		3,550 
		

		

		

		

		

		

		

	
	
		3-16
		
		Portion of longterm debt that will come due in the current period.
		Short Term Portion of LT Debt
		12 
		15 
		25 
		30 
		36 
		

		

		

		

		

		

		

	
	
		3-17
		
		All other current liabilities such as taxes payable, advances from customers, etc.
		Other Current Liabilities
		1,050 
		1,250 
		1,480 
		1,590 
		1,301 
		

		

		

		

		

		

		

	
	
		3-18
		Total Current Liabilities
		4,312 
		6,245 
		7,375 
		8,590 
		9,687 
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		3-19
		
		Longterm bank loans, bonds, mortgages, capital lease obligations, etc.
		Longterm Debt / Borrowings
		1,160 
		1,750 
		2,600 
		3,600 
		3,950 
		

		

		

		

		

		

		

	
	
		

		3-20
		
		All other noncurrent liabilities, such as pensions, product warranties, and future obligations recognized.
		Other Longterm Liabilities
		650 
		750 
		701 
		890 
		995 
		

		

		

		

		

		

		

	
	
		

		

		Total Non Current Liabilities
		1,810 
		2,500 
		3,301 
		4,490 
		4,945 
		

		

		

		

		

		

		

	
	
		

		3-21
		Total Liabilities
		6,122 
		8,745 
		10,676 
		13,080 
		14,632 
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Equity
		3-22
		
		Total Equity from issuing preferred stock.
		Preferred Equity
		0 
		0 
		0 
		0 
		0 
		

		

		

		

		

		

		

	
	
		3-23
		
		Common stock equity at par value.

		Common Equity
		2,044 
		2,005 
		2,069 
		2,090 
		2,120 
		

		

		

		

		

		

		

	
	
		3-24
		
		Common Equity above par value.
		Additional Paid in Capital
		5,013 
		4,900 
		5,159 
		5,626 
		5,628 
		

		

		

		

		

		

		

	
	
		3-25
		
		Transfer of earnings from various periods less dividends paid to shareholders.
		Retained Earnings
		5,097 
		7,050 
		9,840 
		15,050 
		20,005 
		

		

		

		

		

		

		

	
	
		3-26
		
		Foreign currency translations accounted for under the Current Rate Method are reported in the Balance Sheet. Foreign currency translations accounted for under the Temporal Method are reported in the Income Statement. 
		Adj for Foreign Currency Transl
		275 
		120 
		(550)
		(2,147)
		(6,722)
		

		

		

		

		

		

		

	
	
		3-27
		
		Stock buybacks and held in treasury at purchase cost. Reported as a negative amount to equity.
		Treasury Stock
		(1,405)
		(1,460)
		(1,480)
		(1,520)
		(1,550)
		

		

		

		

		

		

		

	
	
		3-28
		Total Shareholder Equity
		11,024 
		12,615 
		15,038 
		19,099 
		19,481 
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		Total Liabilities & Equity
		17,146 
		21,360 
		25,714 
		32,179 
		34,113 
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		3-29
		
		Make sure your Assets + Liabilities balance out with your total Equity. If not, the cell will appear in red with the out-of-balance amount. 
		Check: Assets = Liab + Equity ?
		0 
		0 
		0 
		0 
		0 
		

		

		

		

		

		

		

	
	
		

		

		Comment =>
		Balances
		Balances
		Balances
		Balances
		Balances
		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

		

		

		

		

	
	
		Additional Information
		3-30
		
		Isolate any nondepreciable fixed assets for standardization, such as land or work in progress.
		NonDepreciable Fixed Assets
		0
		0
		0
		0
		0
		

		

		

		

		

		

		

	
	
		3-31
		
		Arises from timing differences between financials and tax return. 
		Deferred Taxes
		112
		101
		90
		98
		109
		

		

		

		

		

		

		

	
	
		3-32
		Goodwill Write Off
		0
		0
		0
		0
		0
		

		

		

		

		

		

		

	
	
		3-33
		
		Average number of common shares outstanding. Rounded to fit with the Income Statement. If no major issues of stock and/or retirements took place during the period, then it's OK to use year-end outstanding shares.

		No of Common Shares o/s
		1,320
		1,290
		1,302
		1,345
		1,322
		

		

		

		

		

		

		

	
	
		3-34
		Par Value of Common Stock
		$10.00
		$10.00
		$10.00
		$10.00
		$10.00
		

		

		

		

		

		

		

	
	
		3-35
		No of Preferred Shares o/s
		0
		0
		0
		0
		0
		

		

		

		

		

		

		

	
	
		3-36
		Par Value of Preferred Stock
		

		

		

		

		

		

		

		

		

		

		

		

	
	
		3-37
		
		Fair market value (price) of stock at end of period.
		Market Price of Common Stock
		$22.65 
		$28.90 
		$37.05 
		$33.60 
		$29.40 
		

		

		

		

		

		

		

	
	
		3-38
		
		Fair market value (price) of preferred stock at the end of the period. 
		Market Price of Preferred Stock
		$0.00 
		$0.00 
		$0.00 
		$0.00 
		$0.00 
		

		

		

		

		

		

		

	
	
		3-39
		
		Preferred dividends in arrears based on par value of preferred x dividend rate x shares outstanding x number of years in arrears.
		Preferred Dividends in Arrears
		0
		0
		0
		0
		0
		

		

		

		

		

		

		

	
	
		3-40
		
		Par value of preferred stock plus any premium that must be paid to retire preferred stock.
		Liquidating value of Preferred Stk
		0
		0
		0
		0
		0
		

		

		

		

		

		

		

	
	
		3-41
		
		Total Equity (Net Assets) less any preferred stock dividends in arrears and the liquidation value of outstanding preferred stock.
		Book Value per Share
		$8.35 
		$9.78 
		$11.55 
		$14.20 
		$14.74 
		

		

		

		

		

		

		

	
	
		3-42
		Dividends per Common Share
		$1.01 
		$1.49 
		$1.89 
		$1.75 
		$1.76 
		

		

		

		

		

		

		

	
	
		3-43
		Dividend Payout Ratio
		45.47%
		38.61%
		39.44%
		29.76%
		30.24%
		

		

		

		

		

		

		

	
	
		3-44
		
		All dividends declared for the reporting period on preferred stock. 

		Cash Dividends to Preferred Stock
		0 
		0 
		0 
		0 
		0 
		

		

		

		

		

		

		

	
	
		3-45
		
		All dividends declared and appropriated for the reporting period on common stock. Dividends actually paid per the Cash Flow Statement have been used to simplify the analysis. 
		Cash Dividends to Common Stock
		1,330 
		1,918 
		2,461 
		2,354 
		2,329 
		

		

		

		

		

		

		

	
	
		3-46
		Total Dividends Paid
		1,330 
		1,918 
		2,461 
		2,354 
		2,329

The words contained in this file might help you see if this file matches what you are looking for:

...Sheet control menu financial analysis amp forecasting prepared by matt h evans cpa cma cfm purpose of spreadsheet revised home active worksheet general input wksh balance income statement to illustrate concepts related and cash flow key financials the uses a combination ratios industry averages ratio benchmark evaluate performance company trend line graphs are also horizontal vertical generated comparing s with pro forma simple projection model linear finally historical information is used prepare set exponential smoothing scenario statements using both nonlinear functions budget final budgets required inputs you will need collect for several reporting periods if want against then setup capture five annual quarterly monthly all fields highlighted in yellow best results sec filings suggested since these reports provide more detail than published note small red triangle upper right corner cell indicates that comment has been inserted point your mouse over appear boxes describe accounts o...

Related files

Share

Help

Financial Analysis & Forecasting			Prepared by Matt H. Evans, CPA, CMA, CFM
			Prepared by Matt H. Evans, CPA, CMA, CFM
Purpose of Spreadsheet:			Revised:	11/22/2002		Home - Active Worksheet active	General Input Wksh2
						Balance Sheet Wksh3	Income Statement Wksh4
To illustrate concepts related to financial analysis and forecasting.						Cash Flow Statement Wksh5	Key Financials Wksh6
The financial analysis uses a combination of ratios and industry averages to						Ratio Analysis Wksh7	Benchmark Analysis Wksh8
evaluate the financial performance of the company. Trend line graphs are also						Horizontal Analysis Wksh9	Vertical Analysis Wksh10
generated, comparing the company's performance with the industry averages.						Pro Forma Financials - Simple Projection Model Wksh11	Pro Forma Financials - Linear Trend Model Wksh12
Finally, the historical information is used to prepare a set of pro forma						Pro Forma Financials - Exponential Smoothing Wksh13	Scenario Analysis Wksh14
financial statements using both linear and non-linear functions.						Budget Analysis Wksh15	Final Budgets Wksh16

Required Inputs:

You will need to collect financial statements for several reporting periods. If you
want to benchmark the performance against the industry, then you will also need
to collect industry averages. The spreadsheet is setup to capture five reporting
periods (annual, quarterly, monthly). All input fields are highlighted in yellow.
For best results, SEC Filings are suggested since these reports provide more
detail than published financial statements.

Note: A small red triangle in the upper right corner of a cell indicates that a comment has
been inserted. Point your mouse over the cell and the comment will appear.					Comment boxes are used to describe accounts, ratios, and other information used in this spreadsheet! Whenever you see a small red triangle, point your mouse over this cell for additional information.

If a cell appears in red, this indicates a warning concerning a calculation.

Worksheets:

This spreadsheet consists of the following worksheets, divided into three sections:

A) Input Worksheets for financial analysis using historical data:

	Worksheet	Title	Purpose
	2	General Input	Enter general information here - used on several worksheets.
	3	Balance Sheet	Enter comparative balances sheets for up to five periods.
	4	Income Statement	Enter comparative income statements for up to five periods.
	5	Cash Flow Statement	Enter comparative cash flow statements for up to five periods.

		Caution: If you enter less than five years of historical information, certain worksheet
		formulas may have to be revised.

B) Output Worksheets for evaluating financial performance:
	6	Key Financial Data	Calculates key financial information for further analysis.
	7	Ratio Analysis	Calculates a series of ratios for further analysis.
	8	Benchmark Analysis	Compare ratio analysis to industry averages.
	9	Horizontal Analysis	Horizontal analysis with corresponding trend lines.
	10	Vertical Analysis	Common size financials in percentages and graphs.

C) Pro Forma / Forecasted Financials for Budgeting:

	11	Pro Forma - Simple	Set of pro forma financials using simple assumptions
	12	Pro Forma - Regression	Set of pro forma financials using linear trending
	13	Pro Forma - Exponential	Set of pro forma financials using exponential smoothing
	14	Scenario Analysis	Example of Scenario Analysis and Goal Seek Analysis
	15	Budget Analysis	Preliminary budget analysis
	16	Final Budgets	Set of budgets per various assumptions and forecasts.

	Note: Some additional worksheets (Answer Reports 1 & 2) may appear in the spreadsheet
	due to the running of Solver.
Macros:

No macros have been used in this spreadsheet to give everyone some assurance that no viruses
are contained in the spreadsheet. However, you are free to add your own macros to save time.
Tools > Macro > Record New Macro

Excel Functions:

This spreadsheet uses certain financial functions (such as =TREND) which might not be
found in your version of Microsoft Excel. To take full advantage of financial and statistical
functions, you should install the Add On package titled: Analysis TookPak. Go to the main
tool bar, select Tools => Add-Ins => check the Analysis TookPak option, insert your
Excel CD and install the Analysis ToolPak. Also, you might want to install the Solver
Add-in since this is useful for solving special forecasting issues (such as finding the
optimal exponential factor).

Compatibility:

This spreadsheet was created with Microsoft Excel 2000. Older versions of Excel (such as 97)
may not be compatible with this spreadsheet.

Corrections:
With any “attempt” to build an Excel Model, I can easily make some mistakes.
So if you have suggestions to make the model better, drop me an email
and I’ll be glad to improve the financial model. My email address is:					matt@exinfm.com

	General Input Panel				Return to 1st Worksheet Home	General Input active
	The following general information should be entered:				Balance Sheet Wksh3	Income Statement Wksh4
	Note: Sample data has been entered in the input cells to help you get started.				Cash Flow Statement Wksh5	Key Financials Wksh6
					Ratio Analysis Wksh7	Benchmark Analysis Wksh8
2-1	Name of Company =>	X Y Z Corporation USA			Horizontal Analysis Wksh9	Vertical Analysis Wksh10
					Pro Forma Financials - Simple Projection Model Wksh11	Pro Forma Financials - Linear Trend Model Wksh12
2-2	Reporting Periods =>	Annual	(Annual, Semi-annual, Quarterly or Monthly)		Pro Forma Financials - Exponential Smoothing Wksh13	Scenario Analysis Wksh14
					Budget Analysis Wksh15	Final Budgets Wksh16
2-3	Number of Days in Reporting Period are		365

	What reporting periods will be entered?

2-4	Most Current Period	2000	(1999, July 1998, 6/30/97, etc.)
2-5	Previous Period	1999	(1999, July 1998, 6/30/97, etc.)
2-6	2nd Previous Period	1998	(1999, July 1998, 6/30/97, etc.)
2-7	3rd Previous Period	1997	(1999, July 1998, 6/30/97, etc.)
2-8	4th Previous Period	1996	(1999, July 1998, 6/30/97, etc.)

2-9	Number of historical periods to be analyzed			5

	How are the amounts expressed in the financial statements?
	(such as: in millions of dollars, thousands of Canadian dollars, etc.)
2-10	millions of dollars

	Home	Balance Sheet for
>>	>	X Y Z Corporation USA
<<	<		millions of dollars
	End		Annual	Annual	Annual	Annual	Annual
			Period	Period	Period	Period	Period
	Ref	Description	1996	1997	1998	1999	2000

Current Assets
	3-1	All cash on hand and any deposits that can be converted into cash. Restricted Cash should be Other Current Assets Cash and Cash Equivalents	990	950	901	998	870
	3-2	Can be held long-term, but is held for the purpose of earning a return on idle cash. Short Term Marketable Securities	10	15	12	6	11
	3-3	Receivables from sales of products on account. Notes Receivalbe should be reported in Other Current Assets for accurate ratio analysis. Accounts Receivable	1,020	1,550	1,830	2,250	3,040
	3-4	Assets held for sale in ordinary business. All cost such as storage, handling, etc are part of inventory. There forms of inventory are Raw Materials, Work in Progress, and Finished Inventory. Inventory	1,005	1,360	1,650	1,900	2,060
	3-5	All other current assets not otherwise categorized, such as prepaid items. Other Current Assets	870	1,150	1,370	1,650	1,530
	3-6	Total Current Assets	3,895	5,025	5,763	6,804	7,511

NonCurrent Assets	3-7	Noncurrent tangible assets used in business operations, such as equipment, vehicles, machinery, land, etc. Fixed Assets	14,006	17,605	21,826	26,950	28,100
	3-8	Cumulative depreciation taken on fixed assets. Accumulated Depreciation	(1,280)	(1,700)	(2,100)	(2,550)	(3,010)
	3-9	Net Fixed Assets	12,726	15,905	19,726	24,400	25,090
	3-10	All longterm investments at cost. Longterm Investments	360	320	120	590	905
	3-11	Investments under the Equity Method in other companies Investments in Other Companies	65	0	0	250	412
	3-12	All other non current assets, such as goodwill, trademarks, etc. Intangibles and Other Assets	100	110	105	135	195
		Total Non Current Assets	13,251	16,335	19,951	25,375	26,602
	3-13	Total Assets	17,146	21,360	25,714	32,179	34,113

Currrent Liab	3-14	Payables related to normal trade transactions with suppliers, vendors, etc. Does not include notes payable. Accounts Payable	2,050	3,150	3,290	3,870	4,800
	3-15	Short term loans, notes payable, commercial paper, etc. Short Term Borrowings	1,200	1,830	2,580	3,100	3,550
	3-16	Portion of longterm debt that will come due in the current period. Short Term Portion of LT Debt	12	15	25	30	36
	3-17	All other current liabilities such as taxes payable, advances from customers, etc. Other Current Liabilities	1,050	1,250	1,480	1,590	1,301
	3-18	Total Current Liabilities	4,312	6,245	7,375	8,590	9,687

	3-19	Longterm bank loans, bonds, mortgages, capital lease obligations, etc. Longterm Debt / Borrowings	1,160	1,750	2,600	3,600	3,950
	3-20	All other noncurrent liabilities, such as pensions, product warranties, and future obligations recognized. Other Longterm Liabilities	650	750	701	890	995
		Total Non Current Liabilities	1,810	2,500	3,301	4,490	4,945
	3-21	Total Liabilities	6,122	8,745	10,676	13,080	14,632

Equity	3-22	Total Equity from issuing preferred stock. Preferred Equity	0	0	0	0	0
	3-23	Common stock equity at par value. Common Equity	2,044	2,005	2,069	2,090	2,120
	3-24	Common Equity above par value. Additional Paid in Capital	5,013	4,900	5,159	5,626	5,628
	3-25	Transfer of earnings from various periods less dividends paid to shareholders. Retained Earnings	5,097	7,050	9,840	15,050	20,005
	3-26	Foreign currency translations accounted for under the Current Rate Method are reported in the Balance Sheet. Foreign currency translations accounted for under the Temporal Method are reported in the Income Statement. Adj for Foreign Currency Transl	275	120	(550)	(2,147)	(6,722)
	3-27	Stock buybacks and held in treasury at purchase cost. Reported as a negative amount to equity. Treasury Stock	(1,405)	(1,460)	(1,480)	(1,520)	(1,550)
	3-28	Total Shareholder Equity	11,024	12,615	15,038	19,099	19,481

		Total Liabilities & Equity	17,146	21,360	25,714	32,179	34,113

	3-29	Make sure your Assets + Liabilities balance out with your total Equity. If not, the cell will appear in red with the out-of-balance amount. Check: Assets = Liab + Equity ?	0	0	0	0	0
		Comment =>	Balances	Balances	Balances	Balances	Balances


Additional Information	3-30	Isolate any nondepreciable fixed assets for standardization, such as land or work in progress. NonDepreciable Fixed Assets	0	0	0	0	0
	3-31	Arises from timing differences between financials and tax return. Deferred Taxes	112	101	90	98	109
	3-32	Goodwill Write Off	0	0	0	0	0
	3-33	Average number of common shares outstanding. Rounded to fit with the Income Statement. If no major issues of stock and/or retirements took place during the period, then it's OK to use year-end outstanding shares. No of Common Shares o/s	1,320	1,290	1,302	1,345	1,322
	3-34	Par Value of Common Stock	$10.00	$10.00	$10.00	$10.00	$10.00
	3-35	No of Preferred Shares o/s	0	0	0	0	0
	3-36	Par Value of Preferred Stock
	3-37	Fair market value (price) of stock at end of period. Market Price of Common Stock	$22.65	$28.90	$37.05	$33.60	$29.40
	3-38	Fair market value (price) of preferred stock at the end of the period. Market Price of Preferred Stock	$0.00	$0.00	$0.00	$0.00	$0.00
	3-39	Preferred dividends in arrears based on par value of preferred x dividend rate x shares outstanding x number of years in arrears. Preferred Dividends in Arrears	0	0	0	0	0
	3-40	Par value of preferred stock plus any premium that must be paid to retire preferred stock. Liquidating value of Preferred Stk	0	0	0	0	0
	3-41	Total Equity (Net Assets) less any preferred stock dividends in arrears and the liquidation value of outstanding preferred stock. Book Value per Share	$8.35	$9.78	$11.55	$14.20	$14.74
	3-42	Dividends per Common Share	$1.01	$1.49	$1.89	$1.75	$1.76
	3-43	Dividend Payout Ratio	45.47%	38.61%	39.44%	29.76%	30.24%
	3-44	All dividends declared for the reporting period on preferred stock. Cash Dividends to Preferred Stock	0	0	0	0	0
	3-45	All dividends declared and appropriated for the reporting period on common stock. Dividends actually paid per the Cash Flow Statement have been used to simplify the analysis. Cash Dividends to Common Stock	1,330	1,918	2,461	2,354	2,329
	3-46	Total Dividends Paid	1,330	1,918	2,461	2,354	2,329

Related files

Share

Share to social media

Help

Login Area