FIN 100 Principles of Finance

Financial_Analysis_Worksheets.zip

Home >Business & Finance homework help >Accounting homework help >FIN 100 Principles of Finance

Financial Analysis Worksheets/BlackScholes.xlsx

A

Generic Algorithm		FINANCIAL ANALYSIS ALGORITHMS
Black-Scholes Model		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	Enter values for all input variables.
Input Variables:
30.00	Stock price
33.00	Strike price
6.00%	Annual risk-free rate
90.00%	Annual volatility
0.75	Years till expiration
8.30	Market price of call
Output Variables:
87.19%	Implied Volatility
8.577	B-S Call Value
10.125	B-S Put Value
8.577	Time Value
0.000	Intrinsic Value
0.627	Hedge ratio
WORKSPACE:
0.9315510617
30	-0.4542594225	0.9299544565	-0.4441781997	0.9328157261	0.9328157307	365	-0.4441781997	0.9328157262	0.0280827498
1.5936982732	0.6274713456	0.9047927195	0.9328157261	0.906708506	0.906708509	0.3109242666	0.9067085062	0.906708506	0
	0.324820996	0.378399745	0.906708506	0.3801172806	0.3801172832	-0.444178184		0.3801172806	0.8719172502
365	0.3251634409	0.3598333413	0.3801172806	0.361466605	0.3614666075	0.6220709373	0.906709846	0.361466605	0.9328157261
0.3251634409	-0.4542594225	0.6274713456	0.361466605	0.6220709458	0.6220709373	0.3284568229	0.310924289	0.6220709458	0.906708506
	365	0.675179004	0.6220709458	0.6715431828	0.6715431771	0.3109242666	0.0280827499	0.6715431828	0.3801172806
		0.9328158218	0.6715431828	365	8.2999995661	-0.444178184	0.000000001	0.0280827498	0.361466605
0.533710763	0.0285011882	0.8719172502	0.6220709458	0.3063339959	1.6075337072	0.0281024815	0.8719172501	0	0.6220709458
5.1474971341	0.0041325257	0.8719172502	0.6220709458	-0.4409771838	-0.4441781997	0.0001948654	0.3063339959	0.8719172502	0.6715431828
0.366289237	0.8714988118	0.0280827498	0.3284568172	0.6203248574	0.6220709458	0.8718975185	-0.4409771838	8.3	365
0.0280836808	0.0370793784	0	0.310924289	0.3296146997	0.3284568172	0.0280827498	0.9337418738	1.6075336852	0.310924289
0.0000091948	0.0889114355	0.0280827498	-0.4441781997	8.2999908052	0.310924289	0	0.9073185096	8.3	-0.4441781997
0.8719163192	0.8629206216	0	0.3801172806	1.6075341511	8.2998051346	0.8719172502	0.3806561737	1.6075336852	365
3.1525028659	8.2958674743	0.8719172502	0.361466605	8.2999999795	1.6075435591	0.0280827518	0.361979057	0.6220709458	0.310924289
1.9999855808	1.6077431222		0.6220709458	1.6075336862	365	0.0000000205	0.6203248574	0.3284568172	-0.4441781997
0.3793679639	0.9067085692	0.0280827937	0.6715431828	8.3	0.310924289	0.8719172482	0.6703853003	0.310924289	0.6220709458
365	0.3989423	0.0000004339	8.2110885645	1.6075336852	-0.4441781997	0.9328157261	365	365	0.3284568172
0.0000086601	0.3107111098	0.8719172063	1.612058566	365	0.3284568172	0.6244568123	0.3109142371	0.3109242879	0.310924289
-0.3172071243	-0.4440290006	0.90673692	0.3107111098	0.3109238147	0.3109242889	-0.4441781997	-0.4441711634	-0.444178199	-0.4441781997
0.5000036048	0.6219899101	0.3801424679	-0.4440290006	-0.4441778677	-0.4441781997	0.6715406394	0.6220671249	0.6220709454	8.299999999
0.3755431877	0.3285107496	0.3614905565	0.932858697	0.6220707655	0.3801173366	8.3	0.3284593606	0.3284568175	1.6075336852
0.0000086601	0.3801184686	0.6219899101	0.3284569372	0.3801172807	0.3614666583	1.6075336852	0.3109142371	0.3109242879	0.906708506
-0.3172071243	0.3614677347	0.6714892504	0.3109238147	0.3614666051	0.6220707655	0.6715431825	-0.4441711634	-0.444178199	365
0.999997994	0.6220671249	0.5000036048	-0.4441778677	0.6220709454	0.6715430628	365	0.9328177522	0.9328157264	0.3109242889

Financial Analysis Worksheets/Bond Value Annual.xlsx

A

Generic Algorithm		FINANCIAL ANALYSIS ALGORITHMS
Bond Value: Annual Coupons		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	Enter values for input variables below containing the
	required rate, par value, maturity, and coupon amount.
Input Variables:
10.66%	Required Rate of Return
1,000.00	Par Value of Bond
16	Number of annual coupons remaining
100.00	Coupon Amount (Annual Interest)
Output Variables:
950.00	Market Value (Price) of Bond
8.43	Duration (Years)

Financial Analysis Worksheets/Bond Value Semi.xlsx

A

Generic Algorithm		FINANCIAL ANALYSIS ALGORITHMS
Bond Value: Semi-Annual Coupons		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	Enter values for input variables below containing the
	required rate, par value, maturity, and coupon amount.
Input Variables:
10.25%	Required Rate of Return (Effective Annual Rate)
1,000.00	Par Value of Bond
20	Number of semi-annual coupons remaining
50.00	Coupon Amount (Semi-annual Interest)
Output Variables:
5.00%	Required (Semi-annual) Interest Rate
1,000.00	Market Value (Price) of Bond
6.54	Duration (Years)

Financial Analysis Worksheets/Char Line Ex.xlsx

A

Example Problem		FINANCIAL ANALYSIS ALGORITHMS
	Beta and the Characteristic Line	A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	In the table below below insert or delete the appropriate
	number of rows so that the number of time periods in the
	table corresponds to your data. Do not delete the first, second
	or last row in the table. If inserting rows, insert in the middle
	of the table. Copy the formula for the first period in cell
	A19 down the column.
	Enter the appropriate market and stock returns in the blue font.
	cells.
	Stock Beta	2.00
Period	Market Return	Stock Return
1	-1.70%	-2.00%
2	-1.70%	4.80%
3	-1.60%	-6.00%
4	1.20%	-4.10%
5	1.70%	1.00%
6	5.10%	6.50%
	-1.70%		-4.37%
	5.10%		9.23%
Standard Deviation	0.02727	0.04962
Sum	0.03	0.00
Intercept	-0.010
Beta	2.000

Characteristic Line

-1.7000000000000001E-2 -1.7000000000000001E-2 -1.6E-2 1.2E-2 1.7000000000000001E-2 5.0999999999999997E-2 -0.02 4.8000000000000001E-2 -0.06 -4.1000000000000002E-2 0.01 6.5000000000000002E-2 -1.7000000000000001E-2 -1.7000000000000001E-2 -1.6E-2 1.2E-2 1.7000000000000001E-2 5.0999999999999997E-2 -1.7000000000000001E-2 5.0999999999999997E-2 -4.3666666666666666E-2 9.2333333333333323E-2

Market Return

Stock Return

Financial Analysis Worksheets/Char Line.xlsx

A

Generic Algorithm		FINANCIAL ANALYSIS ALGORITHMS
Characteristic Line		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	In the table below below insert or delete the appropriate
	number of rows so that the number of time periods in the
	table corresponds to your data. Do not delete the first, second
	or last row in the table. If inserting rows, insert in the middle
	of the table. Copy the formula for the first period in cell
	A19 down the column.
	Enter the appropriate market and stock returns in the blue font.
	cells.
	Stock Beta	1.29
Period	Market Return	Stock Return
1	21.00%	9.64%
2	-15.41%	5.23%
3	38.23%	77.88%
4	21.11%	24.87%
5	11.10%	2.47%
6	15.53%	-1.05%
7	-1.53%	25.39%
8	6.24%	36.80%
9	-22.00%	70.80%
10	15.34%	10.33%
11	-7.07%	-32.60%
12	1.71%	2.21%
13	-2.60%	12.24%
14	-17.73%	-56.26%
15	3.12%	31.60%
16	10.27%	34.37%
17	-16.75%	-29.00%
18	11.40%	24.11%
19	-15.36%	-26.44%
20	23.92%	39.68%
21	-1.06%	11.87%
22	17.70%	9.77%
23	15.26%	33.06%
24	26.65%	51.75%
25	36.64%	54.31%
26	-4.84%	19.86%
27	-8.75%	-38.69%
28	-0.78%	2.96%
29	3.64%	30.47%
30	14.32%	26.15%
31	-7.14%	-5.45%
32	-13.95%	-34.99%
33	13.82%	12.20%
34	25.58%	22.40%
35	17.88%	53.48%
36	17.76%	45.75%
37	-18.76%	-0.05%
38	37.33%	61.76%
39	10.12%	33.65%
40	26.83%	14.02%
41	14.32%	33.38%
42	-14.64%	-46.76%
43	37.66%	85.17%
44	-13.25%	-26.01%
45	26.19%	45.57%
46	38.49%	39.67%
47	-13.86%	2.43%
48	-10.02%	-8.18%
	-22.00%		-21.31%
	38.49%		56.56%
Standard Deviation	0.17563	0.32140
Sum	3.54	7.92
Intercept	0.070
Beta	1.287

Characteristic Line

0.21 -0.15413331699175759 0.38232140073461979 0.21110938320236694 0.111 0.15531308240946617 -1.5329389762680196E-2 6.238139111122698E-2 -0.22 0.15336996898856636 -7.0669668619376985E-2 1.7143838166069292E-2 -2.6032518788919767E-2 -0.17728575760313117 3.122313099023083E-2 0.10269451778878441 -0.16748453335779329 0.11398465538089431 -0.15355471174584256 0.23922321350446007 -1.063965888775123E-2 0.17700900450953244 0.1525990090163325 0.26647907610454841 0.36635442643813004 -4.8381593913720544E-2 -8.7535122248519157E-2 -7.7777800049181334E-3 3.6353057069779177E-2 0.14324608368098168 -7.1436123697261794E-2 -0.13945296454757763 0.13816189606131957 0.25582053765443336 0.17876385410360557 0.17759538359765581 -0.18755177009693222 0.37326452126556203 0.10122776640876674 0.26827319445646525 0.1431923608683006 7 -0.14636746583339183 0.37664799312211855 -0.13254130237000317 0.26189145953513548 0.38490913084113904 -0.13855968576637978 -0.10020957905427094 9.6442401240075415E-2 5.2290048461119787E-2 0.77875491580340639 0.2487127052497074 2.4656696573763182E-2 -1.0451833018502332E-2 0.25386525607394234 0.36801039002745034 0.70804463456222333 0.10331659019310108 -0.32602381383038243 2.2146586215727571E-2 0.1223803958358034 -0.56255785675588399 0.31600035154265088 0.34369718033521229 -0.29003270692444266 0.24107367883432423 -0.26435992353837445 0.39681084755952206 0.11874677506359865 9.7742994882516365E-2 0.33056226292640328 0.51752123692705365 0.54314988853267487 0.19858998248017279 -0.38694208607922459 2.9552101691744048E-2 0.30469926790623497 0.26145315644259087 -5.4528816353809129E-2 -0.34994806858126248 0.12202026231188107 0.22402731067206888 0.53475313266580415 0.45745158797462265 -4.6557711655548228E-4 0.61755633098361007 0.33653175011151643 0.14024874863010756 0.33376082888356967 -0.46763694849114035 0.85170864200958341 -0.260146329355887 0.4556574838685154 0.39673388708893936 2.4282759734601533E-2 -8.1794060576163452E-2 0.21 -0.15413331699175759 0.38232140073461979 0.21110938320236694 0.111 0.15531308240946617 -1.5329389762680196E-2 6.238139111122698E-2 -0.22 0.15336996898856636 -7.0669668619376985E-2 1.7143838166069292E-2 -2.6032518788919767E-2 -0.17728575760313117 3.122313099023083E-2 0.10269451778878441 -0.16748453335779329 0.11398465538089431 -0.15355471174584256 0.23922321350446007 -1.063965888775123E-2 0.17700900450953244 0.1525990090163325 0.26647907610454841 0.36635442643813004 -4.8381593913720544E-2 -8.7535122248519157E-2 -7.7777800049181334E-3 3.6353057069779177E-2 0.14324608368098168 -7.1436123697261794E-2 -0.13945296454757763 0.13816189606131957 0.25582053765443336 0.17876385410360557 0.17759538359765581 -0.18755177009693222 0.37326452126556203 0.10122776640876674 0.26827319445646525 0.14319236086830067 -0.14636746583339183 0.37664799312211855 -0.13254130237000317 0.26189145953513548 0.38490913084113904 -0.13855968576637978 -0.10020957905427094 -0.22 0.38490913084113904 -0.21309471411886038 0.56560673106310666

Market Return

Stock Return

Financial Analysis Worksheets/Fin Math.xlsx

A

Generic Algorithm		FINANCIAL ANALYSIS ALGORITHMS
Compounding & Discounting:		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
Simple Cash Flows		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	Enter values for 3 of the 4 input variables below.
	Leave one input variable equal to zero and the
	worksheet will solve for it.
Input Variables:
- 0	Present Value (PV)
200.00	Future Value (FV)
20.00%	Interest Rate (r)
3.8018	Number of Periods (n)
Output Variables:
	Present Value	Future Value	Interest Rate	Number of Periods
	100.00

Financial Analysis Worksheets/HELP.xlsx

HELP

FINANCIAL ANALYSIS ALGORITHMS
A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
File Names & Descriptions of "Generic" Workbooks
Time Value & Financial Mathematics - Chapter 9
	Fin Math.xlsx	Compounding & Discounting Simple Cash Flows
	Calculator.xlsx	Financial Calculator with Annuity
	Rate Calculator.xlsx	Interest Rate Solver with Uneven Cash Flows
	PV Uneven Cfs.xlsx	Present Value of Uneven Cash Flows
Bond Valuation, YTM & Duration - Chapter 10
	Bond Value Annual.xlsx	Calculate Bond Value & Duration (Annual Coupons)
	YTM Annual.xlsx	Calculate YTM & Duration (Annual Coupons)
	Bond Value Semi.xlsx	Calculate Bond Value & Duration (Semi-Annual Coupons)
	YTM Semi.xlsx	Calculate YTM & Duration (Semi-Annual Coupons)
Stock Valuation - Chapter 10
	Stock Value 1 Stage.xlsx	Stock Value: Constant Growth
	Stock Value 2 Stage.xlsx	Stock Value: 2-Stage Growth
	Stock Value n Stage.xlsx	Stock Value: Multi-Stage Growth
	Stock Value Hybrid.xlsx	Stock Value: Multi-Stage Growth with Terminal Value
	Option Strategies.xlsx	Plot Option Strategies
	BlackScholes.xlsx	Black-Scholes: Call Values & Implied Volatility
Option Valuation - Chapter 11
	Option Strategies.xlsx	Plot Option Strategies
	BlackScholes.xlsx	Black-Scholes: Call Values & Implied Volatility
Investment Risk Analysis - Chapter 12
	Char Line.xlsx	Beta and Characteristic Line (Generic Model)
	Char Line Ex.xlsx	Beta and Characteristic Line (Example)
Financial Analysis and Planning - Chapter 14
	Ratios 4 Year.xlsx	Financial Ratios: 4-Year Model
	Ratios 10 Year.xlsx	Financial Ratios: 10-Year Model
Capital Budgeting - Chapter 17
	NPV IRR MIRR PI.xlsx	Calculate NPV, IRR, MIRR & PI

Financial Analysis Worksheets/NPV IRR MIRR PI.xlsx

NPV IRR MIRR PI

Decision Criteria Calulator		FINANCIAL ANALYSIS ALGORITHMS
NPV, IRR, MIRR &PI		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	Enter values for BLUE FONT cells containing the			Generic Algorithm
	project life, required rate of return and cash flows.
	If the initial investment is an outflow the time zero
	cash flow must be negative.
5	Project life (last year of non-zero cash flows)
10.00%	Required rate of return
Period	Cash	Discount	Present
	Flow	Factor	Value
0	(100,000)	1.00000	(100,000)
1	20,500	0.90909	18,636
2	22,250	0.82645	18,388
3	24,175	0.75131	18,163
4	26,293	0.68301	17,958
5	78,622	0.62092	48,818
6	0	0.56447	0
7	0	0.51316	0
8	0	0.46651	0
9	0	0.42410	0
10	0	0.38554	0
11	0	0.35049	0
12	0	0.31863	0
13	0	0.28966	0
14	0	0.26333	0
15	0	0.23939	0
16	0	0.21763	0
17	0	0.19784	0
18	0	0.17986	0
19	0	0.16351	0
20	0	0.14864	0
	Net Present Value	21,964	NPV
	Internal Rate of Return	16.566%	IRR
	Modified IRR	14.456%	MIRR
	Profitability Index	1.2196	PI

Financial Analysis Worksheets/Option Strategies.xlsx

A

Generic Algorithm		FINANCIAL ANALYSIS ALGORITHMS
Graph Option Strategies		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	Enter values for the blue font parameters in the table below. Press
	enter again when finished. Positive values for "Number" represent
	long positions and negative values represent short positions. For
	"Type", use "C" for a call and "P" for a put.
						Number x
	Type	Number	Price	Strike	Weight	Price
Call 1	C	1.0	5.875	95	213.6%	5.875	1.0
Call 2	C	-2.0	1.625	100	-118.2%	-3.25	2.0
Call 3	C	1.0	0.125	105	4.5%	0.125	3.0
Put 4	P	0.0	5	80	0.0%	0	4.0
Stock		0.0	75		0.0%	0
				Total	100.0%	2.75
Portfolio value t=0 -->			2.75
Starting Value for Stock Price on Graph ->					90.0
	<----------------PROFIT AT EXPIRATION---------------->
	Stock
	Price	Call 1	Call 2	Call 3	Put 4	Stock	NET
0.0	90	-5.875	3.25	-0.125	0	0	-2.75
1.0	91	-5.875	3.25	-0.125	0	0	-2.75
2.0	92	-5.875	3.25	-0.125	0	0	-2.75
3.0	93	-5.875	3.25	-0.125	0	0	-2.75
4.0	94	-5.875	3.25	-0.125	0	0	-2.75
5.0	95	-5.875	3.25	-0.125	0	0	-2.75
6.0	96	-4.875	3.25	-0.125	0	0	-1.75
7.0	97	-3.875	3.25	-0.125	0	0	-0.75
8.0	98	-2.875	3.25	-0.125	0	0	0.25
9.0	99	-1.875	3.25	-0.125	0	0	1.25
10.0	100	-0.875	3.25	-0.125	0	0	2.25
11.0	101	0.125	1.25	-0.125	0	0	1.25
12.0	102	1.125	-0.75	-0.125	0	0	0.25
13.0	103	2.125	-2.75	-0.125	0	0	-0.75
14.0	104	3.125	-4.75	-0.125	0	0	-1.75
15.0	105	4.125	-6.75	-0.125	0	0	-2.75
16.0	106	5.125	-8.75	0.875	0	0	-2.75
17.0	107	6.125	-10.75	1.875	0	0	-2.75
18.0	108	7.125	-12.75	2.875	0	0	-2.75
19.0	109	8.125	-14.75	3.875	0	0	-2.75
20.0	110	9.125	-16.75	4.875	0	0	-2.75
		1.0	1.0	1.0	-1.0
\0\S->	/xmMAIN~
MAIN->	DATA	WORKSHEET
	Enter input values in highlighted cells	Move around the worksheet
	/riINPUT~	/xq
	/xmMAIN~

Call 1 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 -5.875 -5.875 -5.875 -5.875 -5.875 -5.875 -4.875 -3.875 -2.875 -1.875 -0.875 0.125 1.125 2.125 3.125 4.125 5.125 6.125 7.125 8.125 9.125 Call 2 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 3.25 3.25 3.25 3.25 3.25 3.25 3.25 3.25 3.25 3.25 3.25 1.25 -0.75 -2.75 -4.75 -6.75 -8.75 -10.75 -12.75 -14.75 -16.75 Call 3 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 -0.125 -0.125 -0.125 -0.125 -0.125 -0.125 -0.125 -0.125 -0.125 -0.125 -0.125 -0.125 -0.125 -0.125 -0.125 -0.125 0.875 1.875 2.875 3.875 4.875 Put 4 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Stock 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 NET 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 -2.75 -2.75 -2.75 -2.75 -2.75 -2.75 -1.75 -0.75 0.25 1.25 2.25 1.25 0.25 -0.75 -1.75 -2.75 -2.75 -2.75 -2.75 -2.75 -2.75

Financial Analysis Worksheets/PV Uneven Cfs.xlsx

PV Uneven CFs

Generic Algorithm		FINANCIAL ANALYSIS ALGORITHMS
Present Value of		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
Uneven Cash Flows		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	Enter values for blue font cells containing the
	discount rate and each cash flow.
10.00%	Discount Rate
End of	Cash	Discount	Discounted
Period	Flow	Factor	Cash Flow
0	100	1.00000	100.0000
1	0.0	0.90909	0.0
2	0.0	0.82645	0.0
3	0.0	0.75131	0.0
4	0.0	0.68301	0.0
5	(200)	0.62092	(124.1843)
6	0.0	0.56447	0.0
7	0.0	0.51316	0.0
8	300	0.46651	139.9522
9	0.0	0.42410	0.0
10	0.0	0.38554	0.0
11	(400)	0.35049	(140.1976)
12	0.0	0.31863	0.0
13	0.0	0.28966	0.0
14	500	0.26333	131.6656
15	0.0	0.23939	0.0
16	0.0	0.21763	0.0
17	600	0.19784	118.7068
18	0.0	0.17986	0.0
19	0.0	0.16351	0.0
20	700	0.14864	104.0505
		Present Value -->	329.9934

Financial Analysis Worksheets/Rate Calculator.xlsx

A

Generic Algorithm		FINANCIAL ANALYSIS ALGORITHMS
Time Value:		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
Solving for the Rate		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	Enter values for blue font cells containing the rate "guess" and
	each cash flow. There must be at least one positive and one
	negative cash flow.
	If there is more than one rate, change the "guess" to find the
	others.
10.00%	<- Rate Guess
8.413%	<- Rate Answer
End of	Cash
Period	Flow
0	(1,000)
1	100
2	0.0
3	0.0
4	0.0
5	200
6	0.0
7	0.0
8	300
9	0.0
10	0.0
11	400
12	0.0
13	0.0
14	500
15	0.0
16	0.0
17	600
18	0.0
19	0.0
20	700

Financial Analysis Worksheets/Ratios 10 Year.xlsx

A

Generic Algorithm		FINANCIAL ANALYSIS ALGORITHMS
FINANCIAL RATIO ANALYSIS		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	Enter values for blue font cells containing the
	balance sheet and income statement values
	for each year.
	2015.0	2014.0	2013.0	2012.0	2011.0	2010.0	2009.0	2008.0	2007.0	2006.0
BALANCE SHEETS
ASSETS
Cash & marketable securities	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Accounts receivable	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Inventories	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Total current assets	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Gross plant & equipment	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Less: accumulated depreciation	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Net plant & equipment	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Land	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Total fixed assets	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Total assets	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
LIABILITIES & EQUITY
Accounts payable	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Notes payable	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Accrued liabilities	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Total current liabilities	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Long-term debt	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Total liabilities	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Common stock par	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Paid-in capital	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Retained earnings	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Total stockholders equity	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Total liabilities & equity	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
INCOME STATEMENTS
Net revenues or sales	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Cost of goods sold	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Gross profit	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Operating expenses:
General & administrative	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Selling & marketing	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Depreciation	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Operating income	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Interest	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Income before taxes	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Income taxes	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Net income	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Number of shares outstanding	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Earnings per share	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
LIQUIDITY RATIOS
Current ratio (times)	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
Quick ratio (times)	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
Average payment period (days)	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
ASSET MANAGEMENT RATIOS
Total asset turnover (times)	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
Fixed asset turnover (times)	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
Average collection period (days)	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
Inventory turnover (times)	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
FINANCIAL LEVERAGE RATIOS
Total debt to total assets	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
Equity multiplier (times)	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
Interest coverage (times)	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
PROFITABILITY RATIOS
Operating profit margin	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
Net profit margin	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
Operating return on assets	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
Return on total assets	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!
Return on equity	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!	ERROR:#DIV/0!

Financial Analysis Worksheets/Ratios 4 Year.xlsx

A

Example Problem		FINANCIAL ANALYSIS ALGORITHMS
FINANCIAL RATIO ANALYSIS		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	Enter values for blue font cells containing the
	balance sheet and income statement values
	for each year.
Walgreens ($ in millions)	2012.0	2011.0	2010.0	2009.0
BALANCE SHEETS
ASSETS
Cash & marketable securities	1,017.10	449.90	16.90	12.80
Accounts receivable	1,017.80	954.80	798.30	614.50
Inventories	4,202.70	3,645.20	3,482.40	2,830.80
Other current assets	120.50	116.60	96.30	92.00
Total current assets	6,358.10	5,166.50	4,393.90	3,550.10
Net fixed assets	4,940.00	4,591.40	4,345.30	3,428.20
Other long term assets	107.80	120.90	94.60	125.40
Total fixed assets	5,047.80	4,712.30	4,439.90	3,553.60
Total assets	11,405.90	9,878.80	8,833.80	7,103.70
LIABILITIES & EQUITY
Accounts payable	2,077.00	1,836.40	1,546.80	1,364.00
Notes payable	- 0	- 0	440.70	- 0
Other current liabilities	1,343.50	1,118.80	1,024.10	939.70
Total current liabilities	3,420.50	2,955.20	3,011.60	2,303.70
Long-term debt	- 0	- 0	- 0	- 0
Other liabilities	789.70	693.40	615.00	566.00
Total liabilities	4,210.20	3,648.60	3,626.60	2,869.70
Common equity	777.90	828.50	676.30	446.20
Retained earnings	6,417.80	5,401.70	4,530.90	3,787.80
Total stockholders equity	7,195.70	6,230.20	5,207.20	4,234.00
Total liabilities & equity	11,405.90	9,878.80	8,833.80	7,103.70
INCOME STATEMENTS
Revenue	32,505.40	28,681.10	24,623.00	21,206.90
Cost of goods sold	23,360.10	20,768.80	17,779.70	15,235.80
Gross profit	9,145.30	7,912.30	6,843.30	5,971.10
Operating expenses:
Selling, general & admin,	6,950.90	5,980.80	5,175.80	4,516.90
Depreciation	346.10	307.30	269.20	230.10
Operating income	1,848.30	1,624.20	1,398.30	1,224.10
Interest	- 0	- 0	3.10	0.40
Other expense (income)	(40.40)	(13.10)	(27.50)	(39.60)
Income before taxes	1,888.70	1,637.30	1,422.70	1,263.30
Income taxes	713.00	618.10	537.10	486.40
Net income	1,175.70	1,019.20	885.60	776.90
Number of shares outstanding	1031580	1032271	1028947	1019889
Earnings per share	1.14	0.99	0.86	0.76
LIQUIDITY RATIOS
Current ratio (times)	1.86	1.75	1.46	1.54
Quick ratio (times)	0.59	0.48	0.27	0.27
Average payment period (days)	32.45	32.27	31.75	32.68
ASSET MANAGEMENT RATIOS
Total asset turnover (times)	2.85	2.90	2.79	2.99
Fixed asset turnover (times)	6.58	6.25	5.67	6.19
Average collection period (days)	11.43	12.15	11.83	10.58
Inventory turnover (times)	5.56	5.70	5.11	5.38
FINANCIAL LEVERAGE RATIOS
Total debt to total assets	36.91%	36.93%	41.05%	40.40%
Equity multiplier (times)	1.59	1.59	1.70	1.68
Interest coverage (times)	NA	NA	451.06	3060.25
PROFITABILITY RATIOS
Operating profit margin	5.69%	5.66%	5.68%	5.77%
Net profit margin	3.62%	3.55%	3.60%	3.66%
Operating return on assets	16.20%	16.44%	15.83%	17.23%
Return on total assets	10.31%	10.32%	10.03%	10.94%
Return on equity	16.34%	16.36%	17.01%	18.35%

Financial Analysis Worksheets/Stock Value 1 Stage.xlsx

A

Generic Algorithm		FINANCIAL ANALYSIS ALGORITHMS
Stock Valuation: Constant Growth		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	Enter values for all input variables.
Input Variables:
2.00	Dividend Just Paid
5.00%	Rate of Growth in Dividends
18.00%	Required Rate of Return
Output Variables:
2.10	Value of Next Dividend
16.15	Stock Value

Financial Analysis Worksheets/Stock Value 2 Stage.xlsx

A

Generic Algorithm		FINANCIAL ANALYSIS ALGORITHMS
Stock Valuation: 2-Stage Growth		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	Enter values for all input variables.
Input Variables:
1.25	Dividend Just Paid
15.00%	Initial Rate of Growth in Dividends
3.00%	Long Run Sustainable Rate of Growth in Dividends
2.00	Number of periods of initial growth
15.00%	Required Rate of Return
Output Variables:
1.70	Value of dividend number 3
2.50	Present value of the next 2 dividends
13.23	Stock Value

Financial Analysis Worksheets/Stock Value Hybrid.xlsx

A

Generic Algorithm		FINANCIAL ANALYSIS ALGORITHMS
Stock Valuation: Multi-Stage Growth		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
with terminal Stock Value		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	Enter values for all input variables and growth rates
	(all blue font cells).
	Enter one selling (terminal) price for stock in column D.
Input Variables:
1.50	Dividend Just Paid
15.00%	Required Rate of Return
Output Variables:
15.84	Stock Value
Dividend	Growth	Dividend	Terminal	Discount	Present
Number	Rate		Stock Price	Factor	Value
0.0		1.50
1.0	4.00%	1.56	0.00	0.8696	1.357
2.0	4.00%	1.62	0.00	0.7561	1.227
3.0	6.00%	1.72	0.00	0.6575	1.131
4.0	6.00%	1.82	0.00	0.5718	1.042
5.0	6.00%	1.93	0.00	0.4972	0.961
6.0	10.00%	2.13	0.00	0.4323	0.919
7.0	0.00%	2.13	0.00	0.3759	0.799
8.0	0.00%	2.13	0.00	0.3269	0.695
9.0	0.00%	2.13	25.00	0.2843	7.711
10.0	0.00%	0.00	0.00	0.2472	0.000
11.0	0.00%	0.00	0.00	0.2149	0.000
12.0	0.00%	0.00	0.00	0.1869	0.000
13.0	0.00%	0.00	0.00	0.1625	0.000
14.0	0.00%	0.00	0.00	0.1413	0.000
15.0	0.00%	0.00	0.00	0.1229	0.000
16.0	0.00%	0.00	0.00	0.1069	0.000
17.0	0.00%	0.00	0.00	0.0929	0.000
18.0	0.00%	0.00	0.00	0.0808	0.000
19.0	0.00%	0.00	0.00	0.0703	0.000
20.0	0.00%	0.00	0.00	0.0611	0.000
				Sum -->	15.841

Financial Analysis Worksheets/Stock Value n Stage.xlsx

A

Generic Algorithm		FINANCIAL ANALYSIS ALGORITHMS
Stock Valuation: Multi-Stage Growth		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	Enter values for all input variables and growth rates
	(all blue font cells).
Input Variables:
1.50	Dividend Just Paid
18.00%	Required Rate of Return
0.00%	Long Run Sustainable Rate of Growth in Dividends
Output Variables:
8.33	Stock Value
Dividend	Growth	Dividend	Discount	Present
Number	Rate		Factor	Value
0.0		1.50
1.0	0.00%	1.50	0.8475	1.271
2.0	0.00%	1.50	0.7182	1.077
3.0	0.00%	1.50	0.6086	0.913
4.0	0.00%	1.50	0.5158	0.774
5.0	0.00%	1.50	0.4371	0.656
6.0	0.00%	1.50	0.3704	0.556
7.0	0.00%	1.50	0.3139	0.471
8.0	0.00%	1.50	0.2660	0.399
9.0	0.00%	1.50	0.2255	0.338
10.0	0.00%	1.50	0.1911	0.287
11.0	0.00%	1.50	0.1619	0.243
12.0	0.00%	1.50	0.1372	0.206
13.0	0.00%	1.50	0.1163	0.174
14.0	0.00%	1.50	0.0985	0.148
15.0	0.00%	1.50	0.0835	0.125
16.0	0.00%	1.50	0.0708	0.106
17.0	0.00%	1.50	0.0600	0.090
18.0	0.00%	1.50	0.0508	0.076
19.0	0.00%	1.50	0.0431	0.065
20.0	0.00%	1.50	0.0365	0.055
Beyond 20 ---->	0.00%			0.304
			Sum -->	8.333

Financial Analysis Worksheets/YTM Annual.xlsx

A

Generic Algorithm		FINANCIAL ANALYSIS ALGORITHMS
Yield To Maturity: Annual Coupons		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	Enter values for input variables below containing the
	bond price, par value, maturity, and coupon amount.
Input Variables:
1,000.00	Market Value (Price) of Bond
1,000.00	Par Value of Bond
10	Number of years to maturity (coupons remaining)
100.00	Coupon Amount (Annual Interest)
Output Variables:
10.00%	Yield to Maturity
6.76	Duration (Years)

Financial Analysis Worksheets/YTM Semi.xlsx

A

Generic Algorithm		FINANCIAL ANALYSIS ALGORITHMS
Yield To Maturity: Semi-Annual Coupons		A Collection of Microsoft Excel Spreadsheets To Accompany Melicher & Norton's
		INTRODUCTION TO FINANCE, Markets, Investments, and Financial Management, 16e
Instructions:
	Enter values for input variables below containing the
	bond price, par value, maturity, and coupon amount.
Input Variables:
925.85	Market Value (Price) of Bond
1,000.00	Par Value of Bond
20	Number of semi-annual coupons remaining
50.00	Semi-Annual Coupon Amount (Semi-Annual Interest)
Output Variables:
5.63%	Yield to Maturity (Effective Semi-Annual Rate)
11.57%	Yield to Maturity (Effective Annual Rate)
6.40	Duration in Years