Corporate FinanceExcelFinance & BankingFinancial Software

Corporate Financial Analysis with Microsoft Excel Course

Corporate Financial Analysis with Microsoft Excel

Preface

In today’s global economies, spreadsheets have become a multinational language. They are the tools of choice for analyzing data and communicating information across the boundaries that separate nations. They have become an important management tool for developing strategies and assessing results.

Spreadsheets have also become an important tool for teaching and learning. They have been widely adopted in colleges and universities. They have the advantage of being interactive, which makes them ideal for teaching on the Internet as well as self-learning at home.

Corporate Financial Analysis with Microsoft Excel teaches both financial management and spread- sheet programming. Chapters are organized according to the essential topics of financial management, beginning with corporate financial statements.

The text discusses management principles and provides clear, step-by-step instructions for using spreadsheets to apply them. It shows how to use spreadsheets for analyzing financial data and for communicating results in well-labeled tables and charts. It shows how to be better managers and decision makers, not simply skilled spreadsheet programmers.

The text assumes no more knowledge of computers and spreadsheets than how to turn a computer on, how to use a mouse, and how to perform the arithmetic operations of addition, subtraction, multiplication, and division. The first chapter begins with instructions for such basic spreadsheet actions as entering text and data, using cell references to express the relationships between items on spreadsheets and to calculate values, editing and formatting entries, and so forth.

By the end of the text, the reader will have a working knowledge of a variety of financial functions available in Excel for such things as the time value of money and the payoffs of capital investments. He or she will also know how to use Excel’s powerful tools for forecasting, doing sensitivity analysis, optimizing decisions, and using Monte Carlo simulation to evaluate risks. In short, anyone who studies the text will acquire a toolbox of spreadsheet skills that will help him or her understand and apply the principles of financial management—and be better prepared for a successful career in the business world.

 

Models Rather Than Solutions

Corporate Financial Analysis with Microsoft Excel shows how to create models that provide realistic information. Unlike pocket calculators, which are limited in their output, spreadsheet models can supply solutions over a wide range of conditions and assumptions. Models help identify what must be done to achieve desired results, determine the best strategies and tactics for maximizing profits or minimizing losses, identify conditions that must be avoided, or prepare for what might happen. Learning from models is cheaper, faster, and less hazardous than learning from real life. Spreadsheet models make this possible.

 

Managing Risks

Global competition puts a premium on the ability to handle risk. Although it may not appear as a separate item in a CFO’s job description, risk assessment underlies all financial decisions. Risk is a high-stakes game of “What if?” analysis. Corporate Financial Analysis with Microsoft Excel shows how to use Monte Carlo simulation and other spreadsheet tools to gamble like a professional—without the cost. A bit of intelligent programming is the only ante needed to play the game. Spreadsheets help define the risks due to uncertain customer demands, the ups and downs of business cycles, changes by competitors, and other conditions outside a manager’s control. In place of expensive experiments or learning in the school of hard knocks, you can use spreadsheet models to assess the risks and impacts of contemplated actions without actually taking them.

 

Teamwork

Increased worldwide competition and a market-driven economy have forced corporations to restructure their functional hierarchies in ways that promote teamwork. Rigid hierarchies that once divided finance, marketing, production, quality control, and other business functions are disappearing. In their place, functions and responsibilities are being shared in tighter alliances between areas of specialization. These changes extend outside corporate walls to subcontractors and suppliers.

 

The Enabling Role of IT

Information technology (IT) is the essential tool that enables a corporation to think globally and act locally. IT is the backbone of today’s management information systems that corporations use to achieve higher levels of teamwork. Spreadsheets, databases, and special software are the “nuts and bolts” of ERP and other systems that link computer networks and telecommunication systems and that create extended teams.

 

Better Than Algebra

Most students are already familiar with spreadsheets by the time they enter college or complete their fresh- men year. It is safe to say they understand the basic principles of spreadsheets better than those of algebra. Row and column labels transform the values in a spreadsheet’s cells into concrete concepts rather than the abstract notations of algebraic formulas. They help one visualize the logical relationships between variables much better than equations with Xs and Ys. Spreadsheets simply provide a better way than algebra to learn any subject that involves understanding numbers.

 

Communicating

Spreadsheets are used to prepare tables and charts for making presentations that can be easily understood by others and that justify recommended courses of action. Spreadsheets are much more than sophisticated calculators. They are “digital storytellers” that can help you get your message across to others.

 

A Proven Text

Corporate Financial Analysis with Microsoft Excel is the result of the author’s use of spreadsheets for teach- ing financial management over a four-year period. Classes have been conducted at both the graduate and undergraduate level. The text has been used for teaching in a classroom as well as for distance-learning on the Internet (via the CyberCampus system at Golden Gate University in San Francisco).

 

Skills Pay the Bills

Students have found that spreadsheets make learning easier and enhance their understanding of the complexities of financial management. The spreadsheet skills they have acquired have helped many of the author’s students gain employment and earn raises and promotions. That is the success story related by numerous students who have studied Corporate Financial Analysis with Microsoft Excel and applied its teachings.

 

Understanding

Spreadsheets are outstanding pedagogical tools for both teaching and learning. They are akin to the popu- lar Sudoku puzzles in having an arrangement of columns and rows. Like Sudoku puzzles, spreadsheets teach an understanding of the logical relationships between cell entries.

Of course, a spreadsheet for a company’s financial statements, or its month-to-month cash budget, or the projected cash inflows and outflows of expansions of corporate facilities is much larger and complex than a Sudoku grid. Students in the author’s classes have repeatedly stated that financial modeling with spreadsheets helps them understand much better the inner workings of corporations and the strategies and tactics of business management for operating in worldwide markets.

The interactive feature of spreadsheets, with immediate feedback for the results of their decisions in creating and using models, has provided challenges that keep students actively engaged in the process of learning. After more than half a century in the business and educational fields, the author finds spreadsheets to be a most useful pedagogical tool. Student response confirms that belief.

 

An Appreciation

The author has been blessed with an outstanding bunch of students in his graduate classes. Most were working full time to support themselves and their families while attending “distance-learning” classes on Golden Gate University’s CyberCampus. They were mature, most with 10 to 20 years of real-life business experience. Their jobs ranged from entry level to managers and executives, with a few CFOs, CEOs, and vice presidents.

They were eager to learn and invested a great deal of their time in doing the weekly homework assignments and posting their responses to my questions for discussion. They shared their experiences and how they coped with problems. Their places of business and their experience were worldwide—one of the advantages of teaching a class on the Internet. Their feedback has been invaluable in shaping and improving Corporate Financial Analysis with Microsoft Excel, The author is deeply indebted to them.

 

Introduction
An Overview of Financial Management

Before plunging into the creation of Excel models for financial management, it is worth a brief stop to look at the following:

  • The functions and responsibilities of financial managers
  • The position of financial managers and their functions in a corporate hierarchy
  • The relationship of financial management to other functions, such as production and operations, marketing, sales, and quality control.
  • The importance of teamwork and communications
  • The role of information technology in financial management
  • The role of spreadsheet models in financial management

 

Functional Specialization and Linkages

Today’s corporations need many talents—more than any individual or business discipline can provide. Here are a just a few of the more obvious business functions that need different talents:

  • Serving customers
  • Manufacturing a variety of products
  • Conducting research and developing new products
  • Investing in facilities and equipment
  • Controlling the quality of goods and services
  • Ordering and receiving goods from suppliers
  • Distributing goods to worldwide markets
  • Paying workers and suppliers
  • Hiring workers with various types and levels of skills
  • Collecting sales revenues from customers
  • Managing short-term investments and borrowings

Individuals with the different talents needed to operate a business are organized in a hierarchy of departments. At the lowest levels, workers perform the specific functions and responsibilities assigned to them. At the upper levels, managers direct and coordinate the levels below them.

The concept of an organizational structure according to specific functions and responsibilities is simple. Implementing it can be difficult. At their best, business organizations are models of efficiency. At worst, they are wasteful bureaucracies. When bureaucracies run amok, the inevitable results are administrative delays, poor service, shoddy products, late deliveries, high costs, alienated customers, and eventual bankruptcy.

Think of a business as a chain consisting of links. Just as a chain is no stronger than its weakest link, so a business organization is no stronger than its weakest function. And just as a chain is a joining of many links to form a structural network, so business organizations are chains of separate functions joined together in a common enterprise.

Financial managers are an essential part of corporate networks. Their functions are inextricably linked to those of other managers, both financial and nonfinancial. Success depends on how well each does his or her job, and how well they work together as separate parts of the same corporate team.

As we develop financial models in the chapters that follow, keep in mind the concept of chains, linkages, and networks—and the need for all parts to work together.

 

Organizational Charts

Perhaps the quickest way to get a picture of corporate structures and the roles of financial managers is to look at an organizational chart. Figure 0-1 shows a typical organizational chart of the upper and lower levels of management for a manufacturing company. There are many variations on the chart shown, but this functional layout is generally followed.

A Board of Directors is elected by the company’s stockholders to represent their interests as the company’s owners. A corporate board is headed by a Chairman of the Board and typically has a number of standing committees, such as an executive committee, a finance committee, an auditing committee, a human resources and compensation committee, and others.

The president reports to the Board of Directors and is usually designated the firm’s Chief Executive Officer (CEO). Immediately below the company president is the executive vice president, who may be designated the Chief Operating Officer (COO). An administrative vice president and an executive staff are common in large companies. A firm’s legal counsel is usually part of the executive staff. Members of the staff have advisory positions rather than line or functional responsibilities.

Corporate Financial Analysis with Microsoft Excel Figure 0-1
The vice presidents of finance, sales and marketing, and operations are line positions that are responsible for actually carrying out the company’s business. The vice president of finance is usually the company’s chief financial officer (CFO). The essential functions of financial management are separated between those reporting to the company’s treasurer and those reporting to its controller. Data processing is an important support function under the vice president for finance, although it is often a function at the vice-presidental level itself.

The details of how functions and responsibilities are organized vary from company to company and from industry to industry. Therefore, the organizational chart of any company will differ in details from Figure 0-1.

The structure shown in Figure 0-1 for financial management is common to many types of companies. The structures for sales and marketing and for operations, however, vary with the industry. For example, the vice presidents for sales or sales directors of hotels have separate functional managers under them for marketing to conference groups and to the tour industry.

The sales directors of airlines have separate functional managers for passenger sales and cargo sales, as well as administrators responsible for reservations, schedules, and tariffs.

The organizations for companies in service industries also vary from that shown in Figure 0-1, which is typical for a manufacturing company. For example, the operating functions of hotels are divided between a front desk manager, executive housekeeper, chief operating engineer, materials manager, and security manager. The responsibilities of the materials manager are further divided into those for the food and beverage manager, restaurant and café manager, room services manager, and catering manager. Airline operations are divided into flight operations, ground operations, and flight equipment maintenance.

Anyone interested in managing would do well to study organizational charts. Study those of the company for which you work or would like to work. Understand the functions and responsibilities that go with each box, and how the boxes are related to each other. Do the same with the organizational charts of other firms that are available, especially those of competitors. Learn how companies are organized and how your functions and responsibilities interface with others. Companies value employees who know how to be members of their team.

 

A Quick Look at the F&Rs of Chief Financial Officers

Chief Financial Officers (CFOs) are responsible for their firm’s financial management. Their functions and responsibilities (F&Rs) have three major components. The first is categorized as their controllership duties. These are backward-looking activities that deal with compiling and reporting historical financial information such as a company’s financial statements. This information, in turn, is based on the firm’s financial and cost accounting systems.

Shareholders, investors, analysts, and creditors, as well as the company’s upper and lower levels of managers, rely on the accuracy and timeliness of the data and reports for their actions. The second deals with treasury duties. These deal with the firm’s current and ongoing activities, such as: deciding the firm’s capital structure (i.e., determining the best mix of debt, equity, and internal financing); deciding how to invest the company’s money, taking into consideration risk and liquidity; and managing cash inflows and outflows.

The third deals with strategic planning. This is a forward-looking activity. It includes economic forecasting of the future of the company and the impacts on it of future changes in markets, competition, and the general economy. It includes using forecasts to position the company for future profitability and long-term survival.

 

Teamwork

Organizational charts show the division of functions and responsibilities. Teamwork is what puts them back together and combines the parts into an effective organization. Success depends on how well the parts work as a team. Collaboration is more than just a good idea; it is the only way to survive the chal- lenges that confront modern corporations.

As companies grow in size, the administrative levels between executives at the top and workers at the bottom grow in number. The administrative levels form a loop that routes directions and commands down to the line organizations, which actually provide service and goods to customers, and then collects information at the working level and passes it up in the form of reports to those at the top. Top-heavy structures discourage teamwork and reduce efficiency.

Entrenched ways of thinking or doing business are difficult to displace. A crisis is often needed to provide the impetus for change. World War II was such a crisis. It brought together the team that created the world’s first atomic bomb, the weapon that hastened the end of World War II.

The code name for the atomic bomb’s development was the Manhattan Project. It assembled a team of scientists, led by the brilliant physicist J. Robert Oppenheimer, and sequestered them in an isolated community in the Jemez Mountains of New Mexico. That community became the city of Los Alamos. The team was given the unlimited support of the U.S. government under the direction of Major General Leslie R. Groves.

The incredibly complex technical details of the bomb’s development need not be recited here. What is significant is that the members of the team surmounted all the problems and detonated the first man-made atomic explosion at the Trinity Site in New Mexico on July 16, 1945. The team accomplished this feat in only 28 months. To put this accomplishment in perspective, Detroit automakers still take three years or more to design a new automobile and get it into production.

The demonstration at Trinity Site was followed three weeks later, on August 6, with the dropping of the first atomic bomb on Hiroshima, Japan. On August 9, a second bomb was dropped on Nagasaki. Japan gave up the struggle five days later. On September 2, formal surrender ceremonies were held aboard the battleship USS Missouri that ended World War II.

What the Manhattan Project demonstrates so well is the power of interdisciplinary teamwork. That it took a wartime crisis to bring such a team together is also worth noting. As attempts to change peacetime private industry have since demonstrated, it often takes a corporate crisis to overcome the resistance to change administrative structures. The crisis needed to restructure an entrenched hierarchy into an effi- cient workforce may be a face-to-face confrontation with bankruptcy as the alternative.

Despite opposition to change, the concepts of interdisciplinary teamwork that succeeded at Los Alamos are being applied today to make corporations more effective and competitive. Their focus is on doing a job, not on maintaining an organization. The purpose of the organization is to do the work, rather than the purpose of the work is to justify an administrative hierarchy. Corporate restructuring is based on that simple concept—to determine how best to provide goods and services to customers, and then to organize the functions around the activities and processes for doing the work.

The concept of teamwork appears throughout this book. In real life, it is often disguised as a buzzword like “concurrent design” when discussing product design and the interactions between design engineers, manufacturing specialists, and procurement personnel. Financial managers use the term “activity-based costing” for accounting systems that identify the activities and organizations responsible for costs rather than collecting costs at an aggregate level, which rather defeats the purpose of cost control. (The importance of activity-based costing is discussed in Chapter 8: Cash Budgeting.)

Other terms are “total quality management” or “TQM” when the focus is on product quality, and “Just-in-Time” or “JIT” when discuss- ing inventory management. Learn the buzzwords because they are part of today’s jargon. More than that, learn what they really mean, and don’t let any huckster con you into a myopic view of how to apply them. Many with shortsighted understandings failed when they tried to implement buzzword concepts without recognizing their widespread consequences. The true essence of each is teamwork—truly corporate-wide teamwork that enlists personnel in the corporate enterprise regardless of their workplaces and to whom they report.

In terms of the organizational structure of Figure 0-1, teamwork means eliminating the up-and-down ladders of administrative levels that keep workers from working together. Instead of imposing vertical movements along “chains of command,” the corporate structure is “flattened out” so that workers can move horizontally between organizations and work as teams.

 

Information Technology and Management

Computer-based management information systems (MISs) are management tools that facilitate team- work. Their development can be traced to computer-based cost accounting systems and to the Materials Requirements Planning systems introduced into factories in the 1970s. Today’s MISs are corporate-wide. They go by various names, such as Enterprise Planning Systems. In a very real sense, they replace the rungs on administrative ladders.

Figure 0-2 shows the Forecast-Plan-Implement-Control Loop that is an important part of an MIS. Each box contains a brief summary of what it should contain.

MISs collect detailed data on a variety of costs, how well services and goods satisfy quality requirements, how well customers are satisfied, and other criteria used to evaluate business operations. They accumulate the data in huge databases. Spreadsheets and special software are used to withdraw values from the databases and convert them to information, and then assemble the information in the form of reports, tables, and charts.

Corporate Financial Analysis with Microsoft Excel Figure 0-2
Information technology and MISs have expanded the boundaries of teamwork. It is no longer necessary or desirable to sequester team members in an isolated community that forces them to work together, as in the wartime Manhattan Project. Members of international teams now communicate in computer- based languages they all understand, draw data for analysis from common databases, and exchange information at electronic speeds.

Figure 0-3 is a simple example of the concept of using information technology to show organizational linkages and promote understanding and teamwork. It is a spreadsheet that shows the essential elements of a corporate income statement, and the results of different strategies to improve profits. (We will look at the details of income statements and other financial statements in the first and later chapters.) The spreadsheet is simply a matrix of rows and columns for organizing the information. To simplify discussion, the row numbers and column code are included in Figure 0-3.

Corporate Financial Analysis with Microsoft Excel Figure 0-3

Column B shows the base conditions. The company has annual revenues of $1 million (Cell B9). Its cost of goods sold (COGS) is 60 percent of the sales revenues, its selling and other expenses are 30 percent of the sales revenues, and its tax rate is 35 percent of its net operating income (Cells B5:B7).

With these starting values, COGS is computed as $600,000 in Cell B10 (i.e., 60% of $1 million, or the product of the values in Cells B5 and B9) and the firm’s gross profit is computed as $400,000 in Cell B11 (i.e., revenues of $1 million in Cell B9 minus COGS of $600,000 in Cell B10).

Selling and other expenses are computed as $300,000 in Cell BB12 (i.e., 30% of $1 million, or the product of the values in Cells B6 and B9). Net operating income is computed as $100,000 in Cell B13 (i.e., Cell B11 minus Cell B12). Taxes of $35,000 are computed in Cell B14 (i.e., 35% of $35,000, or the product of the values in Cells B7 and B13). After-tax profit of $65,000 is computed in Cell B15 (i.e., net operating income in Cell B13 minus taxes in Cell B14).

Columns C, D, and E show the results for three different strategies the company might use to increase profit. Strategy 1 is to increase sales by 10 percent, strategy 2 is to reduce selling and other expenses by 10 percent, and strategy 3 is to reduce COGS by 10 percent.

Implementing the first strategy requires actions primarily by the sales and marketing organizations, implementing the second requires actions by various organizations, and implementing the third requires actions by the operations organization (e.g., improve any or all of the functions shown in Figure 0-1 for which the vice president of operations is responsible). Note that a change of 10 percent in different functions produces significantly different changes in profits.

Although the example is intentionally simple, it illustrates some important concepts. First, financial results follow from actions in the various parts of a corporation. The results follow a path expressed by the linkages between functions on the organization chart of Figure 0-1. Second, the linkages between organizations and between actions and results can be expressed by entries in the cells of spreadsheets.

Third, as a result of the first two, spreadsheets are extraordinarily powerful management tools—not merely for calculating results for given conditions, but also for analyzing the interactions between organizations and for evaluating the impacts of actions taken by different parts of a corporation. It also follows that spread- sheets are powerful management tools for promoting teamwork across organizational boundaries, as well as powerful teaching tools for understanding business functions and their interrelationships.

 

Communicating

Spreadsheets are much more than sophisticated calculators. Their value is as much for communicating as for calculating. They can help provide transparency into a corporation’s workings. They are easily incorporated into reports and management presentations.

Successful CFOs need to communicate clearly, fully, and honestly. Failure to do that caused a number of corporate scandals in the recent past. Investors were misled by financial shenanigans at Enron, WorldCom, Global Crossing, and other major corporations that filed for bankruptcy.

Between 1997 and 2000, for example, Enron reported an annual growth of 70 percent in its annual revenues and 35 percent in operating profit by moving debt off its books and other accounting tricks. Too late, lenders and other investors discovered that true revenues were lower than reported, debt levels were higher, and prospects for growth were less favorable. They began suing corporations and their executives, threatening them with large dollar penalties and prison terms. In addition to honesty, they insisted on better transparency into corporate performance.

Chief financial officers are no longer narrowly focused on the mechanics of finance. Duties that once were transaction-intensive are now knowledge-intensive.

Today’s CFOs must be forward looking. They are responsible for planning that looks far into the future and across broader markets. They are strategic partners in negotiating global alliances, managing the risks of huge gambles, and organizing new corporate structures. They work closely with corporate boards, the investment community, financial markets, and government regulatory agencies.

Together with chief executive officers, CFOs are the faces of business. They are the leaders for creating teams at the corporate and working levels.

 

Spreadsheets as Tools for Financial Management

Life is complex. The famed naturalist John Muir likened the natural environment to a giant spider web. “Touch just one strand and the whole web vibrates in response,” he pointed out. Corporations are also like giant spider webs of interlocking functions and responsibilities. What happens in one part affects all.

Today, the functional elements of large corporations are linked together by system of computers and software called enterprise management systems. The largest and best known of these are ORACLE and SAS. Such systems do the “heavy lifting” for managing corporate-wide operations. Yet, even in corporations with such systems, many managers have installed Excel on their office computers and use it for accessing information from corporate databases, analyzing it, and preparing reports.

Excel is entirely adequate for handling many business problems. It is more convenient, more accessible, and less costly than enterprise management systems. Indeed, using a large enterprise management system to analyze problems that spreadsheets can handle easier and faster is like using an elephant gun to shoot squirrels. Each has its proper place and use.

Excel is essentially a complete small-scale enterprise management system itself with substantial power. Unfortunately, its capabilities are largely under-appreciated and overlooked. Excel can handle much larger problems than most users recognize—including those who have used it for years. It is flexible and can solve a wide diversity of financial and other business problems.

Students in the author’s classes have all, in fact, used Excel before, many for ten years or longer on their jobs. Yet none were fully aware of all that Excel offers for doing their jobs better and easier. Practically none had used it for doing Monte Carlo simulation or sensitivity analysis.

Few knew how to use Excel to evaluate the impact of changes in corporate strategies or tactics. Few knew how to use Excel to calculate the risks for operating in an uncertain world. Many, in fact, had been unaware of even such simple Excel tools as sorting, conditional formatting, and regression analysis or the commands for the time value of money. You don’t need an enterprise management system to do these things. What you need is to improve your Excel spreadsheet skills, which is one of this book’s goals.

But sound financial management requires more than spreadsheet skills. This book is also intended to help you apply spreadsheet skills to improve your management skills.

As a financial manager, don’t take a narrow view of your job. Recognize your relationship to others in the business. Understand their functions and responsibilities as well as your own, and how they’re related. Understand the linkages in teamwork and how to make them. Use Excel in any financial analysis to help link the functions in corporate networks, just as enterprise management systems can do. And use Excel to communicate and coordinate as well as to calculate.

Back to top button
Close