Financial Analysis With Microsoft Excel 2002

by Mayes, Timothy R.; Shank, Todd M.

• ISBN13: 9780324178241
• ISBN10: 0324178247
• Format: Paperback
• Copyright: 2003-05-27
• Publisher: South-Western College Pub

Summary

FAME, 3rd Edition, is designed to teach learners to use Microsoft and uses Excel 2002 to build spreadsheets to solve financial problems. It starts with the basics in Chapter 1 and quickly moves into more advanced features of Excel. The book covers basic financial statements, ratio analysis, forecasting, time value of money, capital budgeting, and risk and diversification. Many learners will use this book and learn very valuable and highly marketable, skills.

Table of Contents

Preface	xiii
Purpose of the Book	xiv
Target Audience	xv
A Note to Students	xv
Organization of the Book	xv
Outstanding Features	xvi
Pedagogical Features	xvii
Supplements	xvii
Typography Conventions	xviii
Changes from the 2nd Edition	xix
A Note on the Internet	xx
Acknowledgments	xxi
Spreadsheet Basics	1	(40)
Spreadsheet Uses	2	(1)
Starting Microsoft Excel	2	(1)
Parts of the Excel Screen	3	(4)
The Title Bar	3	(1)
The Menu Bar	4	(1)
The Toolbars	5	(1)
The Formula Bar	5	(1)
The Worksheet Area	6	(1)
Sheet Tabs	6	(1)
Status Bar	7	(1)
Navigating the Worksheet	7	(9)
Selecting a Range of Cells	8	(2)
Using Named Ranges	10	(1)
Entering Text and Numbers	11	(1)
Formatting and Alignment Options	11	(3)
Number Formats	14	(1)
Borders and Shading	15	(1)
Entering Formulas	16	(5)
Copying and Moving Formulas	18	(2)
Mathematical Operators	20	(1)
Parentheses and the Order of Operations	20	(1)
Using Excel's Built-In Functions	21	(5)
Using the Insert Function Dialog Box	23	(2)
Using Macro Functions	25	(1)
Creating Graphics	26	(6)
Creating Charts in a Chart Sheet	26	(2)
Creating Embedded Charts	28	(1)
Formatting Charts	29	(3)
Changing the Chart Type	32	(1)
Printing	32	(2)
Saving and Opening Files	34	(1)
Saving Worksheets for the Internet	34	(1)
Using Excel with Other Applications	35	(1)
Quitting Excel	36	(1)
Summary	36	(1)
Problems	37	(2)
Internet Exercise	39	(2)
The Basic Financial Statements	41	(26)
The Income Statement	42	(10)
Building an Income Statement in Excel	42	(5)
Custom Number Formatting	47	(2)
Common-Size Income Statements	49	(1)
Simplifying the Display with Custom Views	50	(2)
The Balance Sheet	52	(4)
Building a Balance Sheet in Excel	53	(2)
Creating a Common-Size Balance Sheet	55	(1)
Building a Statement of Cash Flows	56	(5)
Using Excel's Outliner	61	(1)
Summary	62	(2)
Problems	64	(1)
Internet Exercise	65	(2)
The Cash Budget	67	(34)
The Worksheet Area	69	(3)
Collections	70	(1)
Purchases and Payments	71	(1)
Collections and Disbursements	72	(2)
Calculating the Ending Cash Balance	74	(9)
Using the Cash Budget for Timing Large Expenditures	78	(1)
The Scenario Manager	79	(4)
Adding Interest and Investment of Excess Cash	83	(11)
Calculating Current Borrowing	85	(2)
Using the Formula Auditing Tools to Avoid Errors	87	(1)
Debugging Formulas	87	(4)
Calculating Current Investing	91	(1)
Working Through the Example	92	(2)
Summary	94	(1)
Problems	95	(6)
Financial Statement Analysis Tools	101	(38)
Liquidity Ratios	102	(3)
The Current Ratio	103	(1)
The Quick Ratio	104	(1)
Efficiency Ratios	105	(4)
Inventory Turnover Ratio	105	(1)
Accounts Receivable Turnover Ratio	106	(1)
Average Collection Period	107	(1)
Fixed Asset Turnover Ratio	108	(1)
Total Asset Turnover Ratio	108	(1)
Leverage Ratios	109	(4)
The Total Debt Ratio	110	(1)
The Long-Term Debt Ratio	110	(1)
The Long-Term Debt to Total Capitalization Ratio	111	(1)
The Debt to Equity Ratio	111	(1)
The Long-Term Debt to Equity Ratio	112	(1)
Coverage Ratios	113	(2)
The Times Interest Earned Ratio	113	(1)
The Cash Coverage Ratio	114	(1)
Profitability Ratios	115	(6)
The Gross Profit Margin	115	(1)
The Operating Profit Margin	115	(1)
The Net Profit Margin	116	(1)
Return on Total Assets	117	(1)
Return on Equity	117	(1)
Return on Common Equity	118	(1)
The Du Pont Analysis	118	(2)
Analysis of EPI's Profitability Ratios	120	(1)
Financial Distress Prediction	121	(3)
The Original Z-Score Model	122	(1)
The Z-Score Model for Private Firms	123	(1)
Using Financial Ratios	124	(6)
Trend Analysis	124	(1)
Comparing to Industry Averages	125	(2)
Company Goals and Debt Covenants	127	(1)
Automating Ratio Analysis	127	(3)
Economic Profit Measures of Performance	130	(3)
Summary	133	(3)
Problems	136	(1)
Internet Exercise	137	(2)
Financial Forecasting	139	(24)
The Percent of Sales Method	140	(9)
Forecasting the Income Statement	140	(2)
Forecasting Assets on the Balance Sheet	142	(3)
Forecasting Liabilities on the Balance Sheet	145	(2)
Discretionary Financing Needed	147	(2)
Other Forecasting Methods	149	(11)
Linear Trend Extrapolation	153	(1)
Regression Analysis	153	(7)
Summary	160	(1)
Problems	160	(1)
Internet Exercise	161	(2)
Break-Even and Leverage Analysis	163	(20)
Break-Even Points	164	(4)
Calculating Break-Even Points in Excel	166	(1)
Other Break-Even Points	167	(1)
Using Goal Seek to Calculate Break-Even Points	168	(1)
Leverage Analysis	169	(9)
The Degree of Operating Leverage	170	(3)
The Degree of Financial Leverage	173	(2)
The Degree of Combined Leverage	175	(1)
Extending the Example	176	(2)
Summary	178	(2)
Problems	180	(2)
Internet Exercise	182	(1)
The Time Value of Money	183	(30)
Future Value	184	(3)
Using Excel to Find Future Values	185	(2)
Present Value	187	(1)
Annuities	188	(13)
Present Value of an Annuity	189	(3)
Future Value of an Annuity	192	(1)
Solving for the Annuity Payment	193	(2)
Solving for the Number of Periods in an Annuity	195	(1)
Solving for the Interest Rate in an Annuity	196	(2)
Deferred Annuities	198	(3)
Uneven Cash Flow Streams	201	(3)
Solving for the Yield in an Uneven Cash Flow Stream	203	(1)
Non-Annual Compounding Periods	204	(5)
Continuous Compounding	208	(1)
Summary	209	(1)
Problems	210	(3)
Valuation and Rates of Return	213	(42)
What Is Value?	214	(1)
Fundamentals of Valuation	215	(1)
Determining the Required Rate of Return	216	(5)
A Simple Risk Premium Model	217	(1)
CAPM: A More Scientific Model	218	(3)
Valuing Common Stocks	221	(10)
The Constant-Growth Dividend Discount Model	222	(5)
The Two-Stage Growth Model	227	(3)
The Three-Stage Growth Model	230	(1)
Bond Valuation	231	(5)
Bond Return Measures	236	(6)
Current Yield	236	(2)
Yield to Maturity	238	(1)
Yield to Call	238	(2)
Returns on Discounted Debt Securities	240	(2)
Bond Price Sensitivities	242	(6)
Changes in the Required Return	242	(3)
Changes in Term to Maturity	245	(3)
Preferred Stock Valuation	248	(2)
Summary	250	(2)
Problems	252	(1)
Internet Exercises	253	(2)
The Cost of Capital	255	(26)
The Appropriate ``Hurdle'' Rate	256	(3)
The Weighted Average Cost of Capital	257	(1)
Determining the Weights	258	(1)
WACC Calculations in Excel	259	(2)
Calculating the Component Costs	261	(4)
The Cost of Common Equity	261	(2)
The Cost of Preferred Equity	263	(1)
The Cost of Debt	263	(2)
Using Excel to Calculate the Component Costs	265	(2)
The After-Tax Cost of Debt	265	(1)
The Cost of Preferred Stock	266	(1)
The Cost of Common Stock	266	(1)
The Role of Flotation Costs	267	(4)
Adding Flotation Costs to the Worksheet	269	(1)
The Cost of Retained Earnings	270	(1)
The Marginal WACC Curve	271	(6)
Finding the Break-Points	276	(1)
Summary	277	(1)
Problems	278	(1)
Internet Exercise	279	(2)
Capital Budgeting	281	(44)
Estimating the Cash Flows	282	(9)
The Initial Outlay	283	(1)
The Annual After-Tax Cash Flows	284	(1)
The Terminal Cash Flow	285	(1)
Estimating the Cash Flows: an Example	286	(5)
Making the Decision	291	(14)
The Payback Method	291	(2)
The Discounted Payback Period	293	(2)
Net Present Value	295	(103)
The Profitability Index	398	(1)
The Internal Rate of Return	399
Problems with the IRR	300	(2)
The Modified Internal Rate of Return	302	(3)
Sensitivity Analysis	305	(4)
NPV Profile Charts	305	(1)
Scenario Analysis	306	(3)
The Optimal Capital Budget	309	(10)
Optimal Capital Budget Without Capital Rationing	310	(2)
Optimal Capital Budget Under Capital Rationing	312	(7)
Other Techniques	319	(1)
Summary	319	(1)
Problems	320	(5)
Risk, Capital Budgeting, and Diversification	325	(52)
Review of Some Useful Statistical Concepts	326	(5)
The Expected Value	326	(2)
Measures of Dispersion	328	(1)
The Variance and Standard Deviation	328	(2)
The Coefficient of Variation	330	(1)
Using Excel to Measure Risk	331	(29)
The Freshly Frozen Fish Company Example	332	(3)
Introducing Uncertainty	335	(1)
Sensitivity Analysis	336	(5)
Scenario Analysis	341	(3)
Calculating Expected Values in Excel	344	(1)
Calculating the Variance and Standard Deviation	344	(3)
Monte Carlo Simulation	347	(9)
The Risk Adjusted Discount Rate Method	356	(1)
The Certainty Equivalent Approach	357	(3)
Portfolio Diversification Effects	360	(3)
Determining Portfolio Risk and Return	363	(6)
Expected Portfolio Return	363
Portfolio Standard Deviation	360	(8)
Portfolios with More Than Two Securities	368	(1)
Summary	369	(2)
Problems	371	(4)
Internet Exercise	375	(2)
APPENDIX A Excel 2002 Menu Descriptions	377	(10)
The File Menu	378	(1)
The Edit Menu	379	(1)
The View Menu	380	(1)
The Insert Menu	380	(1)
The Format Menu	381	(1)
The Tools Menu	382	(1)
The Data Menu	383	(1)
The Window Menu	384	(1)
The Help Menu	384	(3)
Index	387