(In Production)

OVERVIEW
The need to understand basic principles of Finance for first year Finance undergraduates with the driving force behind the original version of this book which was produced over a number of years without the use of Excel as a learning tool. The book has been totally revised but still includes lots of exercises and re-enforcement tests covering each of the topics at various stages, with Excel 2007 used extensively.
The workbook is divided into two sections, with the first one providing some Excel 2007 skills as well as some Finance principles which are used throughout. The second part covers the basic Finance topics found in most undergraduate courses. Some chapters provide limited opportunity to use Excel's massive calculation power, but do enable us to use its equally powerful graphical facilities.

(460 pages).

Download Sample Chapter

CONTENT

PART A - Excel Tools

Chapter 1 - Excel 2007 - Basic Tools
For people not too familiar with Excel, this is a walk around chapter illustrating the main features of the software including its graphing and calculation features, operational rules and printing features. It is not meant to replace a thorough Excel text but does focus on sections of the software which are particularly useful to us in this text. Of course I have included a section on the ever increasing menu options for spreadsheet formatting, calculation and control.

Chapter 2 - Functions and Formulae
This chapter is really for those who want to be more competent at functions and formulae. The chapter examines a number of Excel functions which are of particular interest to us in developing business applications. Here I cover logical functions, mathematical and simple statistical functions, search functions, function structures when building functions within functions, numeric and text functions. Finally I have included a short section on importing data into Excel for analysis through the Import Wizard feature.

Chapter 3 - Some Advanced Features
Excel 2007 provides us with some very powerful analysis assistance in the business arena. So this chapter deals with each of them, providing both the background to their use and example. The first topic covered include the two simple analysis aids, Goal Seek and Scenario Manager. Then we examine the idea of Optimisation using Solver. Finally the chapter includes the use of Pivot Tables as an analysis and reporting tool.

Chapter 4 - Macros for Excel 2007
Macros are often the only way to solve particular problems in Excel. This chapter introduces Macros to those unfamiliar with them, beginning with Excels automatic macro recorder. It continues by looking at editing and improving macros automatically written before proceeding to develop function macros and useful business applications. The chapter finishes with Userform development, creating a controlled environment for application use.

Chapter 5 - Financial Calculations
Excel has some 50 financial functions from depreciation, through time value (both single sum and series functions) and DCF functions to bond valuation. The chapter includes many basic examples to illustrate both the concepts and the Excel functions which use them. As well as the usual time value functions we examine all of the main variations pertaining to rate, payment, and period. The capter then looks at bonds and debentures as well as zero coupon debt.

Chapter 6 - Forecasting with Excel 2007
The chapter looks as much as the tests on forecasting techniques as it does on the techniques themselves. The types of forecasting methods are examined using Excel functions and menu options, while both static and dynamic data sources are considered separately. A final topic is tradition and non-traditional time series forecasting.
.
PART B - Accounting Applications

Chapter 7 - Accounting with Excel
Although Excel does not pretend to be an accounting package, and indeed now has Office Accounting 2007 as a separate module, it is possible to reflect on basic accounting principles and accommodate simple practices using the application. This chapter looks at journal entries to accounting statements through a trial balance and into cash flow analysis. It concludes with a look at Free Cash Flow reporting as well as concepts of posting entries into worksheets.
.
Chapter 8 - Budgeting Applications
This is one of the most popular applications of Excel in the business environment, so I have spent much time here on cash flow concepts, differentiating cash from accruals, working capital and budget processes and amendments. Systematic budgeting adjustment processes such as those practiced in many large corporates and public sector entities are considered. Project cash flows and a complete manufacturing budgetary system are developed in the chapter.

Chapter 9 - Statement Analysis
Once the statements exist for the entity, it is a matter of considering the type of analysis which would be useful to the various stake holders. Ratios, both accounting and market based, are considered in terms of the interested parties. Applications with automatic warning levels using target values are developed as well as common size financial reports.

PART C - Investment

Chapter 10 - Break-Even and Leverage
This chapter examines the simplest and the sophisticated approaches to break-even using the power of Excel, extending the concept of break-even to target values and to leverage. It begins by looking at both accounting and cash-flow break-even situations. Non-cash effect models are developed together with both operating and financial leverage consequences. The chapter includes non-linear cost and revenue calculations (using Excel graphing power and regression tools).

Chapter 11 - Capital Budgeting and Investment
In this chapter two distinct corporate issues are covered. The first is concerned with calculating the weighted average cost of capital (WACC) and showing its importance for  inter-company comparisons, and as a target value for deciding on optimum capital structures. The second section of the chapter examines long term investment applications where cash flow is king. Various methods to evaluate wealth creating opportunities are developed, either as new capital ventures, or as capital extensions. The chapter concludes by examining leasing from a lease or buy perpective.

Chapter 12 - Mergers and Acquisitions
The expression mergers and acquisitions refers to corporate growth through the acquisitions of third party organisations rather than internal organic expansion. Whether the process is friendly or hostile, it makes an important difference to shareholders and the size of their slice of the cake created by the acquisition, merger or takeover. The chapter introduces synergy, and the common methods of calculation before considering the forces which lead to its distribution.

Also examined are the alternative acquisition applications which consider strategies such as cash, script and other purchasing alternatives.

Chapter 13- Options and Derivative Trading
This chapter begins by examining the basic pricing theory of options to value financial instruments such as warrants which have some option characteristics, including both Binomial and Black-Scholes approaches. The chapter then considers portfolio insurance strategies centred around the use of Call and Put options as part of portfolio management. A model for enhancing income through option trading is included.

Chapter 14 - Optimisation and Simulation Applications
Much of business is about future planning, and the prediction of asset values, cash flows, and their associated risk. This chapter considers risk, uncertainty and probability as concepts, before looking at the Excel functions which allow simple application in Finance environments. The chapter looks at the use of Monte Carlo simulation to develop applications predicting financial outcomes in various fields.

PART D - International and Personal Applications

Chapter 15 - International Finance
Common calculations and concepts are covered in the chapter including spot and forward exhange rate and transactions calculations. It also develops examples using cross-rate forwards and short dates. The chapter includes applications involving arbitrage and overall hedging strategies. The idea of currency swaps is covered with Excel examples and applications for overall management of exchange rate risk.

Chapter 16 - Personal Finance Applications
This chapter is concerned mainly with personal asset management. The first topic looks at personal budgeting principles before examining the role of gearing in personal wealth creation. This is followed by tax calculation using the ATO formula for personal tax. The second part of the chapter looks at superannuation value forecasting with salary sacrificing, and share portfolio management, including efficient portfolios and the concept of correlation in portfolio structure. The chapter also looks at developing a personal loan model and business plan in Excel.