Introduction to Financial Modelling
Principles of Excel as a Modelling Tool
Excel Operations, Structures and Short-Cuts
Introduction to Excel Functions
Applications of Lookup and Reference Functions
Planning and Building Models for Optimal Decision Making

2.7 Creating Models: Translating Relationships into Excel Formulas

The essence of modelling is the use of formulas to express the relationships between the variables.

The simple model for Sales Revenue in the previous chapter can be developed further, in order to include the cost and profit of the business. For example, the Total Cost could be made up of costs which are fixed (such as some staff costs and overheads) and those which vary with volume (such as raw materials) and Profit would be the Sales Revenue less the Total Cost.

The corresponding Excel model requires the use only of standard arithmetic operations. In words:

  • “Sales Revenue is Price multiplied by the Volume in units”.
  • “Variable Cost is Variable Cost per Unit multiples by the Volume in units”.
  • “Total Cost is Variable Cost plus Fixed Costs Overhead”.
  • “Profit is Sales Revenue less Total Cost”.

The image below shows an influence diagram for this situation:

 

and the corresponding Excel model is shown here:

 

(Here, the diagram and model each follow a common colour-coding scheme: Blue text and grey fill for inputs, black text without fill for intermediate calculations, and black text with green fill for calculations which are also considered as outputs.)

Before building models, one should therefore have a mechanism to conceive and plan for the variables to be included, the flow of their logic, and so on.

Scroll to Top
error: Alert: Content is protected !!