Below is the information that will help you to understand the model in a better way.
1. Business Presentation
Located in Beirut, Lebanon’s capital, Libatech LLC, is an Internet Service Provider (ISP) involved in all aspects of the internet and networking solutions, offers super high level service through a number of delivery strategic services including residential, corporate, and educational institutions.
2. Nature of this study and its reason of being
Libatech LLC started operations in 2012. It grew significantly till 2014 where several buyers started to address its owner, who approached me to help make an estimate of its value and to decide whether it was better selling it through establishing a benchmark price as a basis of sales negotiations.
3. Data Collection â€“ Financial Statements
The starting point was the acquirement of the historical financial statements where the historical data are detailed in the first excel sheet called “Historical Data”. This sheet contains balance Sheets and Income Statements for the period 2012-2014. At the extreme right side, I have also included some drivers that give an idea about the financial standing point according to the historical data available, as per below:
a. Evaluation of the business standing point
i. Number of clients, average bill amount and effective collected sum.
In the second sheet called Hsales-Janmayâ€™15 I have detailed the existing situation. Libatech has two kinds of clients Retailers and Resellers. For each section, I have put the monthly number of clients, the average bill amount per month, and what was really collected (Which is not the product of the multiplication of the number of clients and the average amount of bill).
ii. Business Drivers
To the purpose of this study Business drivers are the assumptions made regarding the next semester of 2015 and the years from 2016 till 2020. These growth assumptions are produced on the sheet Fsales-JuneDec’15. Here a monthly growth of 2.86% is shown for the Retailers number of clients and so on for the average bill amount and thus the sales from June till Dec 2o15. Same reasoning applies for the Resellers section. This sheet concludes with the calculated total sales of 2015 being a blend of the historical data from Jan till May and of the forecasted data for the period between June and December 2015.
b. Modelling Revenue & Expenses Drivers
In Sheet “Forecasting N. Income till 2020”, a full and clear explanation is provided about how to configure Income Statements starting 2015 till 2020. Growth Assumptions are available there as well.
4. Balance Sheet Modeling
Sales Growth Rates that are provided in the following Sheet “Forecasting B. Sheets 2015’20” were used as a basis for the balance sheet variations (growth till 2020). Figures are self explanatory.
The a/c Other Current Assets was used to equalize total assets and total liabilities pursuant to the use of the “Goal Seek” function. This function was used after inputting the yearly Net Income (Earning) in the Equity section of the respective balance sheet, to equalize total assets and total liabilities.
5. Balance Sheets Variations
In the following Sheet called “F.B. sheets variations”, the balance sheets of the years 2015 to 2020 were calculated. These variations will be used to forecast the Cash flow statements as can be seen in the same sheet. In the same sheet you can see at the extreme right side additional business drivers that are key parameters helping out in evaluating the business forecasted financial standing knowing that no additional asset acquisitions of financing are expected to happen.
Additional information is also provided about the cash conversion cycle (CCC) and the related ratios.
6. Business Valuation
On the extreme left of the last sheet “Business Valuation” Free Cash Flows were discounted to their Present Values and the Fair Market Value was calculated to provide the benchmark for selling negotiation as targeted in the beginning of the model construction.