Financial Modeling Project - Finance Module, University Name

This project delves into the realm of financial modeling, exploring its significance in business decision-making. It covers the construction and design of financial statements using vertical and horizontal analysis, providing insights into revenue, expenses, and key financial ratios. The project also examines different types of financial models, including the discounted cash flow (DCF) method for company valuation, and presents a 3-statement financial model with projections. Furthermore, it introduces AWS and Amazon Forecast for financial forecasting, emphasizing the importance of sensitivity and scenario analysis in predicting business revenue and expenses. The report utilizes financial applications and excel to showcase the practical application of financial modeling techniques, offering a comprehensive understanding of financial analysis and its role in strategic business planning. The project's analysis provides a detailed understanding of the financial modeling process, offering valuable insights into financial statement analysis, forecasting, and valuation methods.

FINANCIAL MODELLING

TABLE OF CONTENTS
Project Task 1..................................................................................................................................3
1. Executive summary............................................................................................................3
2. Importance and implication of financial modelling in different business scenarios..........3
3. Construction and designing of financial statement using financial application modelling4
4. Different types of the financial models including discounted cash flow method as tool for
company valuation................................................................................................................11
5. 3 -statement Financial model and the projections for 5 years from 2020-20124.............12
6.Conclusion.........................................................................................................................19
TASK 2..........................................................................................................................................19
Introduction of AWS and Amazon forecast.........................................................................19
Concept of financial forecasting and impact of it on business revenue and expenses.........20
Relevance of Sensitivity and Scenario analysis in financial forecasting.............................22
Conclusion............................................................................................................................23
REFERENCES..............................................................................................................................25

Project Task 1
1. Executive summary
This report is based on the topic financial modelling which is being used in the context of
the business organization and its usefulness. The financial modelling is very relevant to the
business organization in respect to taking the meaningful business related decisions. The
increasing importance and the implication of the financial modelling assists the business
organization in taking relevant business related decisions. There are various tools and techniques
which are being used for constructing and designing the business model or the financial model
using excel as the tool.
2. Importance and implication of financial modelling in different business scenarios
Financial modelling is basically the tool which is being used as a tool by the businesses
for the purpose of preparing the financial models in order to evaluate and determine the future
performance level of the company. This is mainly based upon the historical performance and the
assumptions based on which the financial statements of the company are being prepared. For
instance, earlier, transportation studies and the other financial appraisals were considered as the
separate entities but the problem associated with this approach is that sound technical plan can be
created but it will be financially unstable and will be required significant changes in the same
(Claessens and Kose, 2017). This causes rise in the unwanted developmental costs. This
consequently results into making the decision makers who relied upon it to look back at the
decision made. Therefore, for the purpose of developing the successful operational plan, it is
very essential for creating the financial model along with the same so that changes in the one
element and its impact over the other can be easily identified. It can be used in all aspects as it is
useful in all the industry.
Financial models are useful for the users in making effective and appropriate decisions
which is based upon the final outcome derived from the modes prepared. It is utilized by the
management or the business executives in respect to the various aspects such as the raising of
capital whether through debt or equity, undertaking decisions with respect to making the
business acquisitions, expansion of the business like entering into the new market (Sawatzki,
Zmood and Davidson, 2020). It is also used in diversifying the assets and the business units,
budgeting and forecasting, valuation of the business and capital budgeting as well. The financial

modelling is used in the various sectors such as investing banking, financial KPOs, credit rating
agencies, mutual funds, project finance companies, equity research etc.
Financial modelling is basically the core element which is being used by the businesses
for undertaking the major business related decisions in this huge corporate world. It is considered
as the most valuable tool for making up of choices in order to get the right solution. This is also
used in devising the proper and appropriate financial statements of the company that provides a
more valuable insight on the business finances and operation (Almeida-Filho, de Lima Silva and
Ferreira, 2020). In the finance industry the importance of the financial model is very immense
and it is increasing at a very fast rate. The financial modelling enables the businesses in getting
an estimation of the ideas and the risk associated with it in an effective manner. In mathematical
terms, it represents the economic performance of the organization.
3. Construction and designing of financial statement using financial application modelling
Vertical analysis
The vertical analysis is used for expressing each amount given in the income statement
and the balance sheet of the company in the percentage form (Bartczak, 2020). For undertaking
the analysis of income statement, net sales are taken as the base while in balance sheet total
assets and total liabilities is considered as the base for deriving the calculations. In cash flow
statement, the net earnings are taken as the base. The vertical analysis of the given data is stated
below.
Income Statement 2015 2016 2017 2018 2019
Revenue 100.00% 100.00% 100.00% 100.00% 100.00%
Cost of Goods Sold (COGS) 38.26% 40.65% 37.40% 37.41% 38.01%
Gross Profit 61.74% 59.35% 62.60% 62.59% 61.99%
Expenses
Salaries and Benefits 25.91% 19.19% 18.18% 16.16% 16.74%
Rent and Overhead 10.75% 8.57% 7.68% 7.74% 7.57%
Depreciation & Amortization 19.12% 15.37% 13.10% 11.62% 10.67%
Interest 2.45% 2.12% 1.14% 0.63% 0.60%
Total Expenses 58.22% 45.25% 40.10% 36.16% 35.58%
Earnings Before Tax 3.52% 14.10% 22.50% 26.43% 26.41%

Taxes 1.10% 4.11% 6.46% 7.66% 7.69%
Net Earnings 2.43% 9.98% 16.05% 18.77% 18.72%

Balance Sheet 2015 2016 2017 2018 2019
Assets
Cash 74.20% 75.80% 76.47% 78.96% 80.94%
Accounts Receivable 2.25% 2.47% 2.73% 2.66% 2.55%
Inventory 3.45% 4.02% 4.09% 3.94% 3.83%
Property & Equipment 20.10% 17.71% 16.71% 14.44% 12.68%
Total Assets 100.00% 100.00% 100.00% 100.00% 100.00%
Liabilities
Accounts Payable 1.72% 2.01% 2.04% 1.97% 1.92%
Debt 22.09% 20.91% 12.49% 11.22% 10.14%
Total Liabilities 23.81% 22.92% 14.53% 13.19% 12.05%
Shareholder's Equity
Equity Capital 75.10% 71.11% 70.76% 63.59% 57.44%
Retained Earnings 1.09% 5.97% 14.71% 23.21% 30.51%
Shareholder's Equity 76.19% 77.08% 85.47% 86.81% 87.95%
Total Liabilities & Shareholder's
Equity 100.00% 100.00% 100.00% 100.00% 100.00%
Cash Flow Statement 2015 2016 2017 2018 2019
Operating Cash Flow
Net Earnings 100.00% 100.00% 100.00% 100.00% 100.00%
Plus: Depreciation & Amortization 788.25% 153.93% 81.64% 61.93% 56.97%
Less: Changes in Working Capital 363.92% 14.44% 3.68% 3.38% 2.93%
Cash from Operations 524.34% 239.50% 177.96% 158.55% 154.04%
Investing Cash Flow
Investments in Property &
Equipment 606.35% 127.22% 71.17% 56.15% 53.14%
Cash from Investing 606.35% 127.22% 71.17% 56.15% 53.14%

Financing Cash Flow
Issuance (repayment) of debt 0.00% 0.00% -94.90% 0.00% 0.00%
Issuance (repayment) of equity 6871.94% 0.00% 0.00% 0.00% 0.00%
Cash from Financing 6871.94% 0.00% -94.90% 0.00% 0.00%
Net Increase (decrease) in Cash 6789.93% 112.28% 11.89% 102.40% 100.90%
Opening Cash Balance 0.00% 1424.60% 859.83% 687.72% 747.76%
Closing Cash Balance 6789.93% 1536.88% 871.71% 790.12% 848.66%
(Net earnings has been taken as the base.)
Horizontal analysis
This analysis is used for evaluating and determining the changes in the figures with
respect to the base year (Traina, 2018). It is also called trend analysis and it is represented in the
percentage form. It is used for determining the change in value with respect to the base year and
in this tables given below 2015 is taken as the base year.
Income Statement 2015 2016 2017 2018 2019
Revenue 15.76% 11.23% 39.54% 47.81%
Cost of Goods Sold (COGS) 23.01% 25.88% 36.47% 46.86%
Gross Profit 11.27% 30.54% 41.44% 48.39%
Expenses
Salaries and Benefits -14.26% -9.67% -12.96% -4.47%
Rent and Overhead -7.64% -7.99% 0.52% 4.10%
Depreciation & Amortization -6.92% -11.77% -15.16% -17.54%
Interest 0.00% -40.00% -64.00% -64.00%
Total Expenses -10% -11% -13% -10%
Earnings Before Tax 363.24% 722.43% 946.79% 1008.08%
Taxes 333.70% 657.28% 873.78% 935.35%
Net Earnings 377% 752% 980% 1041%

Balance Sheet 2015 2016 2017 2018 2019
Assets
Cash 7.88% 9.37% 25.66% 42.61%
Accounts Receivable 15.76% 28.76% 39.54% 47.81%
Inventory 23.01% 25.88% 34.93% 45.32%
Property & Equipment -6.92% -11.77% -15.16% -17.54%
Total Assets 6% 6% 18% 31%
Liabilities
Accounts Payable 23.01% 25.88% 34.93% 45.32%
Debt 0.00% -40.00% -40.00% -40.00%
Total Liabilities 2% -35% -35% -34%
Shareholder's Equity
Equity Capital 0.00% 0.00% 0.00% 0.00%
Retained Earnings 476.62% 1328.55% 2408.39% 3549.41%
Shareholder's Equity 7% 19% 35% 51%
Total Liabilities &
Shareholder's Equity 6% 6% 18% 31%
Cash Flow Statement 2015 2016 2017 2018 2019
Operating Cash Flow
Net Earnings 376.62% 751.93% 979.84% 1041.02%
Plus: Depreciation &
Amortization -6.92% -11.77% -15.16% -17.54%
Less: Changes in Working
Capital -81.09% -91.39% -89.97% -90.81%
Cash from Operations 118% 189% 227% 235%
Investing Cash Flow
Investments in Property & 0.00% 0.00% 0.00% 0.00%

Equipment
Cash from Investing 0% 0% 0% 0%
Financing Cash Flow
Issuance (repayment) of debt
Issuance (repayment) of
equity -100.00% -100.00% -100.00% -100.00%
Cash from Financing -100% -112% -100% -100%
Net Increase (decrease) in
Cash -92.12% -98.51% -83.72% -83.04%
Opening Cash Balance
Closing Cash Balance 8% 9% 26% 43%
Schedule of working capital
With the help of excel and the financial modeling applications, different types of
statements can be prepared which will help the management in effectively managing the business
operations. The below statement is the consolidated working capital statement of the company
with relevant financial ratios based on certain assumptions are derived.
Consolidated Working Capital 2015 2016 2017 2018 2019
Net Sales 102007 118086 131345 142341 150772
Cost of Sales 39023 48004 49123 53254 57310
Working Capital Balances
Receivables 5100.35 5904.3
6567.2
5
7117.0
5 7538.6
Inventories 7804.6 9600.8 9824.6
10530.
8 11342
Other current assets
Total Non-Cash Current Assets 12904.95 15505.1
16391.
85
17647.
85
18880.
6

Accounts payable 3902.3 4800.4 4912.3 5265.4 5671
Total Non-Debt Current Liabilities 3902.3 4800.4 4912.3 5265.4 5671
Net Working Capital/ (Deficit) 9002.65 10704.7
11479.
55
12382.
45
13209.
6
(Increase)/ Decrease in Working Capital -1702.05 -774.85 -902.9 -827.15
Purchases 49800.2
49346.
8
53960.
2
58121.
2
Ratios & Assumptions
Accounts Receivable, net (Collection period in
days) 18.25 18.25 18.25 18.25 18.25
Inventory (Days outstanding) 73 73 73 72 72
Accounts Payable (Days Payable) 3786.57
3666.6
3
3740.5
5
3740.8
3
Cash Flow from Individual line items
Receivables (net of allowances of $49, $53, $52,
$47 and $51 respectively) -803.95 -662.95 -549.8 -421.55
Inventories -1796.2 -223.8 -706.2 -811.2
Accounts payable 898.1 111.9 353.1 405.6
(Increase)/ Decrease in Working Capital -1702.05 -774.85 -902.9 -827.15

4. Different types of the financial models including discounted cash flow method as tool for
company valuation.
Financial modelling could be used as tool built in the spreadsheet software like excel for
forecasting the financial performance in future. Forecast is based typically historical performance
of the company, assumptions about future and includes preparation of balance sheet, income
statement, cash flows and the supporting schedules. Output of these financial models is used for
the decision making and for performing the financial analysis whether outside or inside of
company.
There are different types of financial models that could be used by the entity
Three statement model
This model is most basic setup for the financial modelling. As implied by the name, in
the model three statements, income statement, cash flow and balance sheet are linked together
with the formulas in the excel. Objectives is of setting up so that all accounts are linked and set
of the assumptions could drive changes in entire model. It is essential to know about how to link
financial statements that requires solid foundation of the finance, accounting and excel skills.
Discounted Cash Flow Model
DCF model is built over 3 statement models for valuation of company based on NPV of
future cash flows of business. DCF models take cash flows from 3 statement model, necessary
adjustments are made and then XNPV function is used for discounting them at present at
WACC. The method is used for estimating the worth of assets using the projected cash flows. It
is also used by the investors when making big purchases for knowing the long term investments.
It operated under time value of money concept (Markovskaya, Holodkova and Radushinsky,
2018). The investors use the model for understanding true value of the start-up before investing
and calculating the stake to buy. Investors also use the fundamental analysis to assess the trade of
company is higher or lower and actual worth.
Leverage Buyout model
LBO transaction involves modelling of complicated debt schedules and is also advanced
form of the financial modelling. LBO is one of most detailed as well as challenging type of the
financial modelling, as there are many layers of the financing creating circular references and als
requires cash flow waterfalls. The models are not common outside the private equity or the
investment banking type (Berrada, Loudiyi and Zorkani, 2017). Model is useful when acquirer

company uses significant debt for financing cost of the acquisitions, to determine fair valuation
and the exit returns of company which is being acquired.
Merger and Acquisitions Model
M&A model is used for evaluating impact of the acquisitions. It is also more advanced
type of the financial modelling that involves making adjustments for creating the Pro Forma
ending date balance sheet, incorporating synergies and the terms of deal and the modelling
dilution/ accretion and performing the sensitivity analysis along with determining expected
impact over the valuation. The model is used when the companies decide for merging for
synergy, high market share, diversifications or company acquiring another company. Analysts
also use the mode for determining accretion or dilution.
Credit Rating Model
The model is used mainly by the credit analysts for assessing creditworthiness of
company. If company is already having debt, analysts will be building financial models for
determining the company has required cash flows for meeting the payments. If metric provides
that company will not be able to meet the covenants of loan agreement and renegotiations of the
credit terms will be arranged. If company is looking for debt, credit analysts would analyse
financial health of company (Duan, 2017). This is used for evaluating legitimate borrowing
potential and interest rate of the company.
5. 3 -statement Financial model and the projections for 5 years from 2020-20124.
3 statement financial model and projections
This a model which forecasts the three financial statements of the company. Accounting
enable to understand the historical financial statements while forecasting financial statements
enable exploring how company would be performing under the variety of assumptions and
visualising how the operation decisions, investing decisions and the financing decisions all
interact for impacting bottom line in future. Well-built financial model help insiders as well as
outsiders to evaluate how activities of the firm work together making things easier for seeing
how the decisions impact overall performance of the business.
Income Statement
2015 2016 2017 2018 2019 2020 2021 2022 2023 2024