1

Financial Analysis with Microsoft Excel

Timothy R. Mayes and Todd M. ShankPublished by Dryden Press ISBN: 9 780030 155024

Reviewed by Marjory Forbes and Brian R DoughtyDepartment of Finance and Accounting

Glasgow Caledonian University

IntroductionThe purpose of the book is to integrate financial management and Excel spreadsheettechniques by encouraging student thinking rather than providing pre-built templates.The Preface provides background to the use of spreadsheets in the business world. Thetext is written for Excel 7.0. No difficulties were encountered when testing with thiswith Excel 5.

This is intended to be a supplementary not a primary text and is similar to anIntroductory Financial Management textbook. However, the authors see the depth ofthe book on the financial management side as providing potential for use as a primarytext. Emphasis is placed on the self-teaching approach and concentration on theinterpretation of results. Particular mention is given to statistics and managementscience tools in this respect, and it is assumed that students will have familiarity of basicaccounting and statistics. The chapters are progressive in terms of financialmanagement content, and contain objectives and summaries.

The authors anticipate that more interest can be generated from using this book with itsself-teaching/self-paced approach than from a traditional lecture. This is especially truein the case of non-finance students. The intended audiences are students on first yearundergraduate financial management courses, first year MBA courses, or any coursewhich is case-oriented with extensive use of spreadsheets

The book begins by introducing spreadsheet basics and moves on through BasicFinancial Statements, Cash Budgets to Capital Budgeting and its attendant risks. Eachchapter is preceded by a list of learning objectives with a summary of the major Excelfunctions being discussed at the end of each.

Spreadsheet Basics including mathematical operators and order of precedence arecovered in Chapter 1.

The techniques used to Format Charts are possibly “too heavy” for Spreadsheet Basicsbut nevertheless, the principles explained are common to many facilities in charting.Unfortunately, the instructions leave the reader with a chart dissimilar to the exhibit, interms of formatting, and without direction as to how to change it.

2

The reader will have spent much time in developing the worksheet before instructionson saving are given. These should have been placed much earlier in the text to providethe reader with the opportunity of saving his/her work and coming back to it a latertime.

Conclusion on the BasicsThere are many good examples in this chapter which are spoilt by a few inconsistenciesand incomplete instructions. The authors claim that “Programming requires the studentto ... confront many issues...” and that “...the book concentrates on spreadsheet buildingskills ... to think and understand”. It is believed that some of these confrontations atthis basic stage will unnerve many students who will be unsure of what to do nextparticularly when the instructions do not quite lead them where they expect to be. Thechapter would be good value in a supervised class with the tutor overriding theseproblem areas.

The contents of the later chapters are summarised below.

Beyond the BasicsThe Basic Financial Statements, Income Statement, Balance Sheet and Cash FlowStatement using Exhibits as references are built. This requires students to produce anIncome Statement, Balance Sheet and Cash Flow statement. The chapter is well writtenintroducing students to the essentials of these reports. However it does not address theissues of terminology. For example, the Income Statement can commonly be referred toas the Profit and Loss Account, Accounts Payable as creditors and Accounts receivableas debtors. No definition of these terms is given, regardless. Fiscal Year may also betermed Tax Year. There is an assumption that students have a familiarity with theseconcepts and others e.g. depreciation methods, breakdown of cost of goods sold.

The Cash BudgetThe cash budget is described and its use in the planning of short-term borrowings andtiming of expenditures is emphasised. The example used is well stated with theexception that the opening balance is not given as an assumption at the start and is only“discovered” in the course of completing the chapter. The problems associated withdetermining the short-term borrowing are addressed and consideration is given tousing the cash budget for timing large expenditures.

The chapter develops to cover adding of interest and investment of excess cash, and thecalculations for cumulative investing/borrowing and cumulative interestincome/expense are explained adequately.

The student is not told to adjust the formula for unadjusted cash balance and endingcash balance to reflect the effects of short-term interest income/(exp.) and CurrentInvesting. It is most unlikely that novices would be able to resolve this difference on

3

their own, making a self-teaching approach difficult. Integration could be better in thissection too.

The problem associated with determining the short-term borrowing is addressed andthe spreadsheet solution of using a logical test (IF statement) to calculate this isintroduced. The description of this statement would probably need some furtherexplanation and it is likely that only the better students will grasp this immediately.

The chapter on Evaluating Performance with Financial Ratios explains the purpose ofratios and who would use them. The categories covered are liquidity, efficiency,leverage, coverage and profitability ratios. Examples are based on the workbookcreated for Chapter 2, Basic Financial Statements.

The contents in this chapter are well presented and the explanations and examples areappropriate. The last section interprets the various ratios to give a company profile.This involves trend analyses and comparison with industry averages. It stresses that noratio can be taken in isolation and that the whole topic is very subjective. A useful ratioformula summary is given at the end of the chapter.

As far as the Excel implementation is concerned, this is a well presented chapteralthough inconsistency between text and illustration again prevailed. Care with theinstructions is also necessary.

Financial ForecastingAgain, progression and integration is achieved by use of the workbook created forChapter 2, Basic Financial Statements. The Percent of Sales Method is used forforecasting of the income statement, forecasting assets on the balance sheet andforecasting liabilities on the balance sheet.

When forecasting liabilities, current liabilities, long-term liabilities and owner’s equityare reviewed. Spontaneous sources of financing and discretionary sources of financingare then introduced. After the forecasting example the balance sheet does not squareand this is used to indicate to the student the need for discretionary financing.

Other forecasting techniques covered are the Trend function, regression analysis and anassessment of risk by calculating a firm’s beta coefficient using regression analysis. Thissection on Linear Trend Extrapolation uses Excel to determine a sales forecast based oncertain mathematical procedures. Students are asked to enter historic data to a newsheet and then prepare an XY chart. The resulting chart gives rise to a need todetermine a linear trend. The Excel function TREND is used to do this. The student isthen taken through the stages of using Excel to insert the trendline on the chart,including displaying the straight line equation which Excel generates!

4

Regression Analysit is handled without any mathematical exposition. Using a filesupplied by the authors, the student can, with care, create a scatter plot using the chartwizard. A few anomalies were encountered. The student is invited to enter theregression line on the chart.

Break-even and Leverage AnalysisThis chapter considers decisions that managers make regarding the cost structure of thefirm which in turn impact on methods of financing the firm and pricing the firm’sproducts. Variable and fixed costs are introduced and are presented graphically.Break-even point is defined specifically as the unit sales required for earnings beforeinterest and taxes to be equal to zero i.e. operating break-even point. A simple exampleis given in which the contribution margin per unit and percentage is introduced. Theexample lends itself to “what if” questions but this is not provided and would assist inemphasising interpretation skills.

Other Break-even Points covered are the sales units required to earn a given level ofincome before interest and tax, but is not really a break-even point. A cash break-evenpoint is also calculated by eliminating any non-cash expenses from the fixed costs,notably depreciation. Nothing is required in Excel at this stage but someimplementation might have enhanced integration.

Under Leverage Analysis Business Risk and Financial Risk are defined beforecalculations are performed for the degree of operating leverage, the degree of financialleverage and the degree of combined (Total) leverage for Spuds and Suds, (i.e. not theoriginal workbook).

The Time Value of MoneyThis looks at general formulas for future value and present value of a single amount, forannuities and the NPV and IRR for streams of uneven cash flows. The problem of non-annual compounding periods is mentioned. The inverse nature of the relationshipbetween FV and PV is stated. There is a good manual example of annuities and thedistinction between regular and deferred annuities is made. NPV is well covered.However, PVF or FVF components of formula are not highlighted and there is nomention of financial tables which students could use to check figures manually.Discounting, and opportunity costs are not explicitly defined. At times, the termsdiscount rate and interest rate are used interchangeably but this convention is notexplained.

In solving for ‘yield’ in uneven cash flow streams, an illustration of manual calculationwould prove useful. The theoretical weaknesses of IRR are inadequately covered, but iselaborated on in the Chapter on Capital Budgeting.

5

The section on non-annual compounding periods is short on manual examples andanswers to calculations are not given for students to receive feedback on theirunderstanding.

6

Valuation and Rates of ReturnThe concepts of value, risk-return trade-off and CAPM, share valuation and bondvaluation are dealt with and in general the chapter explains the material very well.However, definitions are lacking at times e.g. time to maturity, beta, principal, parvalue. Value Line is not explained. With respect to share valuation, it is not totally clearwhich formulas are the Constant Growth Model and which are the Gordon Model.However, manual examples of the Gordon Model are very clear. There is a gooddiscussion on how to convert terms from annual to semi-annual for bond valuation.

Self-teaching for this chapter may prove difficult due to the challenging nature of thetopic. Once again, problems exist which have occurred in earlier chapters and arelargely avoidable - such as incorrect range details, no use of function wizard andincomplete instructions.

The Cost of Capital section includes hurdle rates, WACC and its components, marginalWACC and flotation costs. This chapter is well covered, and uses a new set of examples.

Capital BudgetingThis chapter covers payback, discounted payback, NPV, PI, IRR and MIRR along withsensitivity analysis and producing an optimal capital budget. Overall, this chaptercovers the theory well, using Supreme Shoe Company as the illustration.

Under NPV, it is stated that all positive NPV projects must be accepted, which is correctif there is no capital rationing, but this assumption is not stated at this point in the text.

Risk, Capital Budgeting and DiversificationThis the final chapter and uses a new set of data viz. the Freshly Frozen Fish company.Statistical concepts are reviewed e.g. continuous and discrete probability distributions,expected values, variance/standard deviation and risk-adjusted discount rates,portfolio risk, return and diversification. The chapter focuses on populations where thedistribution is discrete and known, but alternatives for samples where probabilitydistribution are unknown are referred to. Complex theory and formulas are presentedwell.

Certainty equivalence is reviewed, and Decision Trees and Monte Carlo Simulation arecovered although not requiring Excel work. Again, it is assumed students know thebasics and some areas could provide more explanation e.g. Finding the NPV formDecision Trees, and definitions e.g. utility function, diversification (it is defined, but atthe end of the chapter).

In the Excel models some assumptions/explanations regarding formula would help.Having said that, in places, the Excel instructions might help some students understandthe finance theory more easily.

7

Conclusions on FAMEFinancial Management coverage was comprehensive and progression was achieved.The extent of interpretation was appropriate. Overall the integration of financialmanagement concepts with Excel skills worked well.

The book is appropriate for its intended audience. However, if the audience includesnon-finance students but with the assumption that these students will have a basicknowledge of accounting and statistics, then these students may need greater assistancewhen working through the book.

On occasion, American terms are used without footnotes to provide Englishequivalents. Footnotes could also have been used for definitions or references whichare assumed to be known by the student.

Some chapters do take two to three hours to complete and any course would need toallow time for this when integrating the book into an existing programme.

It is recommended that the book it is suitable as a supplementary text, with tutorsupport, as students may have difficulties using it as a primary text on a self-taughtbasis.

Marjory Forbes and Brian R DoughtyDepartment of Finance and Accounting

Glasgow Caledonian University