Financial Modeling-unit 1

Financial modeling is a highly effective method for assessing a company’s financial

performance. It entails building a mathematical model (often Excel) that can assist in performing
the financial statement analysis and helping business professionals make the best company
decisions.
Importance of Financial Modeling
Finance is the most crucial part of any company and contains complex data. And financial
modeling helps in getting a clear understanding of all the components. Also, it helps businesses
make better financial decisions at the heart of its significance. It is frequently used by
organizations for strategic planning. Financial modeling enables scenario planning by simulating
the effects of significant variables so that organizations may determine their course of action in a
variety of potential situations.
Capital budgeting also heavily relies on financial modeling. In addition to simplifying resource
allocation for the upcoming large investment and financial statement analysis, it also aids in
calculating the cost of capital. In addition to the returns anticipated by investors, it gives a
complete analysis of the debt/equity structure for this use.

Basics of Excel
Ribbon: Excel selects the ribbon's Home tab when you open it. Learn how to use the ribbon.
Workbook: A workbook is another word for your Excel file. When you start Excel, click Blank
workbook to create an Excel workbook from scratch.
Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each
Excel workbook can contain multiple worksheets.
Format Cells: When we format cells in Excel, we change the appearance of a number without
changing the number itself.
Find & Select: Learn how to use Excel's Find, Replace and Go To Special feature.
Templates: Instead of creating an Excel workbook from scratch, you can create a workbook
based on a template. There are many free templates available, waiting to be used.
Data Validation: Use data validation to make sure that users enter certain values into a cell.
Keyboard Shortcuts: Keyboard shortcuts allow you to do things with your keyboard instead of
your mouse to increase your speed.
Print: This chapter teaches you how to print a worksheet and how to change print settings.
Share: Learn how to share Excel data with Word documents and other files.
Protect: Encrypt an Excel file with a password so that it requires a password to open it.
 Commands for Excel

 Ctrl+N: Create a new workbook

 Ctrl+O: Open an existing workbook
 Ctrl+S: Save a workbook
 F12: Open the Save As dialog box
 VLOOKUP: Fetch data from a table based on a lookup value
 IF: Perform a logical test and return a value based on the result
 CONCATENATE: Combine two or more values into one text string
 COUNT: Count the number of numerical values in a range
 LEN: Count the number of characters in a cell
 ABS: Convert a negative value to a positive value

Conditional Formatting
Conditional formatting is a feature used to make unique, important, or duplicate values stand out
or to emphasize trends in a data set. As the name suggests, the feature allows you to format the
cells and their data based on conditions you specify, which makes important information easy to
see at a glance.
Some common examples:
Formatting cells that meet specific criteria with a highlight color and/or font
Applying different colors to different cells based on their values
Applying color scales or gradients to visually represent cells’ values.

Pivot table and Pivot Charts

Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the
significance from a large, detailed data set.
Pivot tables are useful for sorting, reorganizing, grouping, counting, totaling or averaging
data stored in a database. They are the perfect solution when you need to summarize and
analyze large amounts of information. Here are the main functionalities of pivot tables:
 Calculate various descriptive statistics of the underlying data
 Filter data based on a specific criterion/criteria
 Create visualizations of the conducted analysis
 Summarize large amounts of data by organizing the data into small conclusive tables
 Create reports and charts to understand trends
 Allow data filters to view the details for areas of interest and explore more by changing
the parameters.
Our data set consists of 213 records and 6 fields. Order ID, Product, Category, Amount, Date and
Country.
Insert a Pivot Table
To insert a pivot table, execute the following steps.
1. Click any single cell inside the data set.

2. On the Insert tab, in the Tables group, click PivotTable.

The following dialog box appears. Excel automatically selects the data for you. The default
location for a new pivot table is New Worksheet.

3. Click OK.
Drag fields
The PivotTable Fields pane appears. To get the total amount exported of each product, drag the
following fields to the different areas.
1. Product field to the Rows area.

2. Amount field to the Values area.

3. Country field to the Filters

area. Sort
To get Banana at the top of the list, sort the pivot table.

1. Click any cell inside the Sum of Amount column.

2. Right click and click on Sort, Sort Largest to Smallest.

Result.

Filter
Because we added the Country field to the Filters area, we can filter this pivot table by Country.
For example, which products do we export the most to France?

1. Click the filter drop-down and select France.

Result. Apples are our main export product to France.

Note: you can use the standard filter (triangle next to Row Labels) to only show the amounts of
specific products.
Change Summary Calculation
By default, Excel summarizes your data by either summing or counting the items. To change the
type of calculation that you want to use, execute the following steps.

1. Click any cell inside the Sum of Amount column.

2. Right click and click on Value Field Settings.

3. Choose the type of calculation you want to use. For example, click Count.
4. Click OK.

Result. 16 out of the 28 orders to France were 'Apple' orders.

Two-dimensional Pivot Table

If you drag a field to the Rows area and Columns area, you can create a two-dimensional pivot
table. First, insert a pivot table. Next, to get the total amount exported to each country, of each
product, drag the following fields to the different areas.
1. Country field to the Rows area.

2. Product field to the Columns area.

3. Amount field to the Values area.

4. Category field to the Filters area.

Below you can find the two-dimensional pivot table.

To easily compare these numbers, create a pivot chart and apply a filter. Maybe this is one step
too far for you at this stage, but it shows you one of the many other powerful pivot table features
Excel has to offer.
Below you can find the pivot table. Bananas are our main export product. That's how easy pivot
tables can be!

What If Analysis
What-If Analysis in Excel is a tool that helps us create different models, scenarios, and Data
Tables.
We have three parts of What-If Analysis in Excel. They are as follows:
1. Scenario Manager
2. Goal Seek in Excel
3. Data Table in Excel
Scenario Manager in What-If Analysis
As a business head, it is important to know the different scenarios of your future project. Based
on the scenarios, the business head will make decisions. For example, you are going to undertake
one of the important projects. You have done your homework and listed all the possible
expenditures from your end, and below is the list of all your expenses.
The expected cash flow from this project is $75 million, which is in cell C2. Total expenses
comprise all your fixed and variable expenses, the total cost is $57.45 million in cell C12. Total
profit is $17.55 million in cell C14, and profit % is 23.40% of your cash inflow.
It is the basic scenario of your project. Now, you need to know the profit scenario if some of
your expenses increase or decrease.
Scenario 1
 In a general case scenario, you have estimated the “Project License” cost to be $10
million, but you are sure anticipating it to be $15 million
 Raw material costs to be increased by $2.5 million
 Other expenses to be decreased by 50 thousand.
Scenario 2
 The “Project Cost” to be at $20 million
 The “Labor Daily Wages” to be at $5 million
 The “Operating Cost” is to be at $3.5 million
Now, you have listed out all the scenarios in the form. Based on these scenarios, you need to
create a table about how it will impact your profit and profit %.
To create What-If Analysis scenarios, follow the below steps.
1. Go to DATA > What-If Analysis > Scenario Manager.

2. Once you click “Scenario Manager,” it will show you below the dialog box.

3. Click on “Add.” Then, give “Scenario name.”

4. In changing cells, select the first scenario changes you have listed out. The changes
are Project License (cell C10) at $15 million, Raw Material Cost (cell C7) at $11
million, and Other Expenses (cell C11) at $4.5 million. Mention these three cells
here.

5. Click on “OK.” It will ask you to mention the new values as listed in scenario 1.
6. Do not click on “OK” but click on “OK Add.” It will save this scenario for you.
7. Now, it will ask you to create one more scenario. As we listed in scenario 2, make the
changes. This time we need to change Project Cost (C10), Labour Cost (C8), and
Operating Cost (C9).

8. Now, add new values here.

9. Now click on “OK.” It will show all the scenarios we have created.

10. Click on “Scenario summary.” It will ask you which result cells you want to change.
Here, we need to change the Total Expense Cell (C12), Total Profit Cell (C14), and
Profit % cell (C16).
 11. Click on “OK.” It will create a summary report for you in the new worksheet.

Total Excel has created three scenarios even though we have supplied only two scenario changes
because Excel will show existing reports as one scenario.
From this table, we can easily see the impact of changes in pour profit %.
#2 Goal Seek in What-If Analysis
Now, we know the Scenario Manager’s advantage. What-if-Analysis Goal Seek can tell you
what you must do to achieve the target.
Andrew is a class 10th student. His target is to achieve an average score of 85 in the final exam.
He has already completed 5 exams and left with only 1 exam. Therefore, in the completed 5
exams.

To calculate the current average, apply the average formula in the B7 cell.

The current average is 82.2.

Andrew’s GOAL is 85. His current average is 82.2. He is short by 3.8 with one exam.
Now, the question is how much he has to score in the final exam to eventually get an overall
average of 85. It can be found by the What-If Analysis GOAL SEEK tool.
 Step 1: Go to DATA > What-If Analysis > Goal Seek.

 Step 2: It will show you below the dialog box.

 Step 3: Here, we need to set the cell first. “Set cell” is nothing but which cell we need for
the final result, i.e., our overall average cell (B7). Next is “To value.” Again, Andrew’s
overall average GOAL is nothing but for what value we need to set the cell (85).
The next and final step is changing which cell you want to see the impact on. So, we need to
change cell B6, the cell for the final subject’s score.
  Step 4: Click on “OK.” Excel will take a few seconds to complete the process, but it
eventually shows the result like the one below.

Now, we have our results here. To get an overall average of 85, Andrew has to score 99 in the
final exam.
#3 Data Table in What-If Analysis
We have already seen two wonderful techniques under What-If Analysis in Excel. First, the Data
Table can create different scenario tables based on the variable change. We have two kinds of
Data Tables here: one variable Data Table and a “Two-variable data table.” Assume you are
selling 1,000 products at ₹15, your total anticipated expense is ₹12,500, and your profit is
₹2,500.
You are not happy with the profit you are getting. Your anticipated profit is ₹7,500. You have
decided to increase your per-unit price to increase your profit, but you do not know how much
you need to increase.
Data tables can help you. Create a table below.

Now, in the cell, F1 links to the “Total Profit” cell, B6.

 Step 1: Select the newly created table.

 Step 2: Go to DATA > What-if Analysis > Data Table.

 Step 3: Now, you will see below dialog box.

 Step 4: Since we are showing the result vertically, leave the ”Row input cell.” In the
“Column input cell,” select cell B2, which is the original selling price.
  Step 5: Click on “OK” to get the results. It will list out profit numbers in the new table.

So, we have our Data Table ready. To profit from ₹7,500, you need to sell at ₹20 per unit.
Things to Remember
 The What-If Analysis data table can be performed with two variable
changes. Refer to our article on What-If Analysis two-variable Data Table.
 What-If Analysis Goal Seek takes a few seconds to perform calculations.
 What-If Analysis Scenario Manager can give a summary with input
numbers and current values together.

Macros

what are macros? The best way to think of macros is as a series of instructions.
You set out a series of steps that you want Word to automate, then tell Word to
perform those steps whenever you like. This makes automation very easy, and can
save a lot of time and effort. The easiest way to create a macro is to allow Word to
record your actions, then physically perform your desired actions within the
document. Once recorded, you can tell Word to repeat what you did at any time.

Making a Macro

In this example we’ll be creating a macro that automatically creates a table. This
might be a useful feature if you occasionally see yourself needing to create a new
table without manually making a new one or copy-pasting an existing table.

First, we need to tell Microsoft Word how to make our table. To do this, we click on “View,” then
“Macros,” then “Record Macro.”

We name our macro here. For this example we’ll call it “CreateTable.”
Underneath the naming box are two buttons, “Button” and “Keyboard.” This is where you pick if
you want to activate your macro via a button within Word or by pressing a hotkey on your
keyboard.

Selecting the Button Option

If you select “Button,” you’ll be taken to a new popup window. It may look confusing at first, but
it’s just Word allowing you to add the button to the Quick Access bar. This is where we want our
button, so we’ll select the macro and click “Add >>” to add it to the list on the right.

Underneath the naming box are two buttons, “Button” and “Keyboard.” This is where you pick if
you want to

activate your macro via a button within Word or by pressing a hotkey on your keyboard.

Selecting the Button Option

If you select “Button,” you’ll be taken to a new popup window. It may look confusing at first,

but it’s just Word allowing you to add the button to the Quick Access bar.

This is where we want our button, so we’ll select the macro and click “Add >>” to add it to the
list on the right.
Selecting the Keyboard Option If you select the keyboard option, you can define which hotkey
to assign the macro to. It’s compatible with quite a few combinations,
such as Ctrl + [Key] and Ctrl + Shift + [Key], so pick out a hotkey that suits you.
Word will let you know if anything is currentlyassigned to that hotkey so
you don’t interfere with existing shortcuts. Once you’ve found the perfect key combination, click
“Assign.”

Recording the Macro

Now that you have set up the method of activation, Microsoft Word will begin recording your
actions. While it’s recording, any actions you now perform in the document will be remembered
in the macro. In this example, we create our table and fill it out with the essential fields. Because
our actions are being recorded, we should be careful and not make any mistakes!
Once the table is done, we click “View,” then “Macro” again, and then click “Stop recording.”

Now that our macro is recorded, we can make this table at any time we like. If we assigned it to a
hotkey on the keyboard, we can press those keys to insert a table. Likewise, if we made a button
for it, we can find that button in the Quick Access bar.

Regardless of which method we selected for the macro, our table is replicated when we activate it.
Now we can recreate our base table with little hassle!

Deleting a Macro

When your macro has outlived its use, you can get rid of them by simply clicking “View,” then
“Macros,” then “View Macros.”
Select the macro you don’t want anymore, then click “Delete.”