Financial Modeling - Starting from Scratch

Folks,

I am excited to announce that we are starting our Financial Model.

The First thing I want you guys to add to your practice is to Design the Financial Model at the initial stage.

The importance of Designing for Financial Model can be elaborated by the following famous quote.

"The number one reason why models fail, and there's not even a close second is Poor Design. Full Stop it is a poor design. It always comes down to poor design" IAN Schnoor

So, designing a financial model is key to a successful financial model. You Should follow some design principles for making an error-free financial model.

1. Plan & design your model in the initial stage
2. Must include Cover Page, Executive Summary & Table of contents
3. Assumption Sheet / Control Sheet on a separate Tab
4. Use different cell styles/colors for distinguishing data i.e. input, calculations, formulas, output
5. Use schedules / separate tabs for calculations
6. Never use hardcore data in formulas & keep formulas simple and easy to understand
7. Build separate sections for timelines, scenario analysis, and sensitivity analysis
8. Make sure that the model is working correctly and producing the expected results.
9. Use charts, graphs, and other visual aids to present the results of the financial model in a clear and concise manner.

By following the above rules we can design user-friendly financial models.

INTRODUCTION OF PROJECT

Our project is related to building a financial model for my "MI Confectionery Factory". Its details are as under:

https://docs.google.com/presentation/d/1WNfMTPz11XHKq1UGCjzYTyCL6WTktwh6/edit?usp=share_link&ouid=103925402771525408193&rtpof=true&sd=true

I am sharing the above details only for your information. We will draft our timeline Sheet today but we will refer to this file from the next newsletter as this file will provide us a basis for Assumption Sheet for our model.

Today, we will design our Sheet for Timelines. Constructing a timeline sheet is the base of a financial model as this will help us to consolidate monthly results into quarterly and annual results with just a few clicks.

Name of TAB

We should name our Tabs so that we can distinguish each tab by its name.

Use of Cell Styles

Cell styles in MS Excel are formatting options that allow users to quickly and consistently apply a specific look to a cell or group of cells. By using cell styles, we can save time and ensure consistency across their spreadsheets. Additionally, we can create their own custom cell styles to meet their specific formatting needs.

Name of Project along with Project Timelines

While finalizing the Model, we will require to link sheets with timelines in order to make it dynamic. We will make one timelines Sheet & will link it with other sheets as and when required.

Special Columns for Additional Working

You may have noticed from the above snap that I have left some blank columns before periods, which I will use for some additional workings. I'll recommend you for following this approach as it will help us in making our formulas consistent across the model.

We have designed it for making a Monthly model for a period of 60 Months. We will use some formulas to make annual results at the end.

Let's Draw our timelines by using some Excel Functions:

Monthly Timelines

Start Date: "IF" function is used to calculate the start date of the period. Currently, I have utilized our extra Col. for the linked Project Start Date. Once the assumption sheet is constructed, we will link it to that sheet.

End Date: For extracting the last day of the Month, EOMONTH function is used.

No of days: By subtracting start date from the end date & adding 1.

Month Number: Simple MONTH function is used.

Running Years: Using YEAR Formula we can get this number easily.

Annual Timelines

Annual Start Date: Same as above.

Annual End Date: Using the same EODATE Formula we can easily extract end date of the year.

Annual Years: Simple YEAR Formula is used.

Quarterly Timelines

We will also add Quatrely flags in order to get quarterly results as and when required. We can achieve this easily by marking flags for the quarter end. Here I use MOD Function for marking these flags. It's a bit tricky as it is not in common use for most of us so let me elaborate with an example before using it in the model.

MOD function returns the remainder of two numbers after division.?For example, MOD(13,3) = 1. The result of MOD carries the same sign as the divisor.

Quater Begining Flag: Using Nested IF function with MOD can lead to marking the period accurately.

Quarter Ending Flag: Same above function by using the same logic for the quarter end as quarter numbers are divisible by 3.

Note: Above results can also be achieved by using alternate formulas as well. I am using the above formulas as per my practice & experience.

I think it's enough for today otherwise you guys may feel it is boring. I am sharing an Excel file as well with you guys so that you may review it at your ease.

Pls, feel free to contact me regarding any query related to the above.

May Allah accept our ibadah, supplications, and efforts during the month of Ramadan.?May Allah give us steadfastness on the path of righteousness, elevate our imaan, cleanse our sins, and bestow his blessings and mercy upon us in this world and in the hereafter. Ameen.

Your Linkedin Mentor,

Mohsin Idrees