Integrated Financial Modelling in MS Excel and VBA

Financial Modeling for a Business Entity

Finance is the core of any business. Whether Charitable, For-Profit or Not-For Profit entities, it may not be an exaggeration to say that finance is needed at every stage, namely, on commencement, for normal operations during existence, for expansions / diversifications and even when the entity is to be formally wound up / liquidated. Whether objectives are strategic or trivial, bear monetary outlook or not, finance is the basic motherboard and functions as a springboard as well depending upon the business scenario. Simultaneously, it needs to be understood that the finance that is invested in a business entity is effectively employed to bring out results in the form of better value addition at any point of time and more importantly on a comparative note, in the light of uncertainties, competing entities or markets, so that the providers of finance are able to get the desired returns.

Given the importance of and the need to effectively utilise finance in a business, it is thus only a necessity that 'effective financial management' is embedded within strategic and operational processes of an entity. Effective Financial Management requires 'best practices' that inculcate habits of incorporating the uncertainties to the extent possible and SWOT environment of a business entity on a 'moving goal post' basis in the financial planning process.

Designing of financial plans that encompasses features such as incorporation of uncertainties, identification of variables - independent and resulting variables that drive the performance of a business entity in the light of changing macro & micro economic and organizational factors in the financial planning process in a dynamic manner and subjecting to validations and multi perspective modular & scenario analysis is referred to as 'Financial Modeling' . Such a model may be either a component financial model or an entity financial model. In other words, important features of a financial model are as follows.

• Incorporates uncertainties to the possible and available extent

• Identifies independent and resulting variables, also known as dependent variables that drive an entity's performance

• Identifies extent of possibility of events that could impact independent drivers

• Incorporates expected impact on dependent drivers as a result of the analysis of the extent of possibility of events

• Validates assumptions and values that are derived on the basis of those assumptions

• Component models or Individual models make up an Entity Financial Model

• Multi perspective and scenario analysis enhances utility of a financial model

• Dynamism is an integral part of a financial modeling process

Benefits of Financial Modeling

From a business entity's perspective, financial modeling is helpful in enabling achievement of its strategic and tactical objectives as it provides a platform for setting up of financial and non-financial goals in the light of expected events, setting of bench marks, projection of financial performance, comparison of projected plans with actual performance, tweaking of organisational strategies and plans in the light of changing events. More specifically, it helps in measuring the performance, ability to generate and deploy cash and worth of a business entity from the perspective of strategic and financial investors, its owners / major shareholders and the Board of Directors /business process managers who are custodians of the organizational resources that are monetary, tangible, intangible, physical, localised or globalised, locational or clouded or otherwise.

Financial Modeling from the perspective of other stakeholders

Apart from owners, board of directors and managers, who are important constituents of a business entity, you would agree that there are other stakeholders as well. Important of them are lenders, fund managers, retail investors, personal financial planners, analysts, investment consultants, share brokers etc., This segment also does build financial models as a part of their regular investment decisions and advices they provide to their clients. The objectives with which the financial models are built by this segment may range from projecting an entity's ability to repay the loans, to that of providing returns in the form of dividend and appreciation in share value.

Tools of Financial Modeling

Since Financial Modeling process as explained earlier involves dealing with uncertainties to a greater extent, tools that are of utility in developing financial models range from that of guestimates /estimates and consensus, moving to Statistical tools such as Moving Averages, Trend Analysis and further to application of neural models.

Excel Based Financial Modeling

It would be redundant to explain the importance and features of spreadsheets in developing financial models and that too of MS Excel, which is a widely used Spreadsheet application software. However, it may be justifiable to mention that Excel offers much more features than that are generally applied in developing financial models.

For whom?

Thus the need for financial modeling arises to every one that is interested in planning and analysis of financials of a business entity. While it is not possible to list all of them, based on our discussion so far, it may be mentioned that the following are the important beneficiaries who need to or would develop financial models relating to that of a business entity.

• Strategic and Financial Investors

• Managers

• Independent Directors

• Lenders

• Retail Investors

• Analysts

• Consultants

• Fund Managers

• Business Media

• Academic Community