Financial Modelling Lab

PRACTICAL FILE

Submitted for partial fulfilment for the award of the Degree of

Bachelor Of Commerce (Hons.)
(B.COM (H) 2019 - 2022)

Under the supervision of

Dr.Swati Narula
Submitted by: -
Name: Yogesh Kumar
Enrolment Number.: - 08417788819

Vivekananda Institute
of Professional Studies
SCHOOL OF BUSINESS STUDIES

VIVEKANANDA INSTITUTE OF PROFESSIONAL STUDIES

(Affiliated to Guru Gobind Singh Indraprastha University)

Yogesh Kumar 08417788819 B.Com-4B FM File

S No. PARTICULARS Page
No.
i. STUDENT UNDERTAKING, CERTIFICATE AND
ACKNOWLEDGEMENT
1. EXCEL INTRODUCTION AND COMMANDS
2. BASIC EXCEL FUNCTIONS
 MATHEMATICAL FUNCTIONS
 LOGICAL FUNCTIONS
 DATE FUNCTIONS
 TEXT FUNCTIONS
 STATISTICAL FUNCTIONS
3. CHARTS & GRAPHS IN EXCEL
4. AUTO FORMAT &CONDITIONAL FORMATTING
5. FILTER-BASIC AND ADVANCED
6. SORTING THE DATA
7. UNDERSTANDING MACROS
8. ADVANCED EXCEL
 DATA TABLES
 GOAL SEEK
 SCENARIO ANALYSIS
 PIVOT TABLE & CHART
 PROBLEM SOLVER
9. INTRODUCTION TO FINANCIAL MODELLING
 MEANING OF FINANCIAL MODELLING AND

Yogesh Kumar 08417788819 B.Com-4B FM File

 FINANCIAL MODEL
 TYPES OF FINANCIAL MODEL
 FEATURES OF A GOOD MODEL
10. ADVANCED EXCEL AS A TOOL IN FINANCIAL
MODELLING:
 VLOOKUP
 HLOOKUP
 DATA VALIDATION
 DEPRECIATION USING SLN AND DB
11. ENTERING HISTORICAL DATA

12. FORECASTING FINANCIAL STATEMENTS

13. RATIO ANALYSIS
14. VARIOUS APPROACHES TO VALUATION
15. SENSITIVITY ANALYSIS
16. TIME VALUE OF MONEY
 PMT
 PV
 FV
 RATE
 PPMT & IPMT
17. CAPITAL BUDGETING TECHNIQUES
 NPV
 XNPV

Yogesh Kumar 08417788819 B.Com-4B FM File

  PI
 IRR
 XIRR
 MIRR
18. INTERNATIONAL CASH FLOWS 105
19. DIVIDEND DISCOUNT MODELS 106-111
20. MARKET BASED METHODS 112-116
 EV/EBITDA
 EV/SALES
21. ANNEXURE 117-130

Yogesh Kumar 08417788819 B.Com-4B FM File

STUDENT UNDERTAKING

This is to certify that I have completed the Project titled

“FINANCIAL MODELLING AND SPREADSHEETS” under the
guidance of “DR. Swati Narula” in partial fulfillment of the
requirement for the award of degree of Bachelor of Commerce (H) at
Vivekananda Institute of Professional Studies, Vivekananda School of
Business Studies, New Delhi. This is an original piece of work and has
not been submitted elsewhere.

STUDENT SIGNATURE:

STUDENT NAME:
Yogesh KUMAR

Yogesh Kumar 08417788819 B.Com-4B FM File

CERTIFICATE

This is to certify that the Project titled “FINANCIAL

MODELLING AND SPREADSHEETS” is an academic work done
by “Yogesh KUMAR” submitted in the partial fulfilment of the
requirement for the award of the Degree of Course from
Vivekananda Institute of Professional Studies.  It has been
completed under the guidance of DR. Swati Narula (Faculty Guide).
The authenticity of the project work will be examined by the viva
examiner which includes data verification, authenticity of
information etc. and it may be rejected due to non-fulfilment of
quality standards set by the Institute.

Signature of the Faculty Guide

Yogesh Kumar 08417788819 B.Com-4B FM File

ACKNOWLEDGEMENT

Every work involves efforts and inputs of various kinds and people. I
am thankful to all those people who have been helpful enough to me
to the extent of their being instrumental in the completion and
accomplishment of the project entitled “FINANCIAL
MODELLING AND SPREADSHEETS”. I am very thankful to DR.
Swati Narula for her timely guidance, supervision & encouragement
that have helped me to get this golden opportunity and who
provided me their expert advice, inspiration & moral support. I thank
her for contributing and guiding me for the same, the valuable
suggestions & guidance provided by her really helped me in
successful accomplishment of my project.
Last but not the least I would like to express my heartiest gratitude
to my family members and friends. Their motivation, love and moral
support boosted my confidence to work sincerely and in an effective
way. Thank you for being my guide and motivator throughout this
difficult path.

S tu d e n t’ s N am e
Y oge s h K UMA R

Yogesh Kumar 08417788819 B.Com-4B FM File

Introduction to Excel

Microsoft Excel is a spreadsheet program that is used to record and analyse numerical data.
Think of a spreadsheet as a collection of columns and rows that form a table. Alphabetical
letters are usually assigned to columns and numbers are usually assigned to rows. The point
where a column and a row meet is called a cell. The address of a cell is given by the letter
representing the column and the number representing a row. Microsoft Excel is one of the
most used software applications of all time. Hundreds of millions of people around the world
use Microsoft Excel. You can use Excel to enter all sorts of data and perform financial,
mathematical or statistical calculations.

Uses of Excel

 One of the best uses of MS Excel is that you can analyse larger amounts of data to
discover trends. With the help of graphs and charts, you can summarize the data and
store it in an organized way so that whenever you want to see that data then you can
easily see it. It becomes easier for you to store data and it will definitely save a lot of
time for you.

 There are so many tools of MS Excel that make your work extremely easy and save
your time as well. There are wonderful tools for sorting, filtering and searching which
all the more make you work easy.

 It makes easy for you to solve complex mathematical problems in a much simpler
way without much manual effort. There are so many formulas in MS Excel and by
using these formulas you can implement lots of operations like finding sum, average,
etc.

 The chief use of MS Excel is that it provides security for excel files so people can
keep their files safe. All the files of MS Excel can be kept password-protected through
visual basic programming or directly within the excel file.

 You can represent data in the form of charts and graphs so it can help in identifying
different trends. With the help of MS Excel, trend lines can be extended beyond
graph and therefore, it helps one in analysing the trends and patterns much easier. In
business, it is very important to analyse the popularity of goods or the selling pattern
that they follow to maximize sales.

Yogesh Kumar 08417788819 B.Com-4B FM File

  Another use of MS Excel is that it can be accessed online from anywhere and
everywhere which means that you can access it from any device and from any
location whenever you want. It provides the facility of working conveniently which
means that if you don’t have laptops then you can use mobile and do your work easily
without any problem.

 Next use of MS Excel is that it helps you in adding more sophistication to your data
presentations which means that you can improve the data bars, you can highlight any
specific items that you want to highlight and make your data much more
presentable.

 Another interesting use of MS Excel is that you can keep all your data at one
location. This will help you in saving your data from getting lost. It will keep all your
data in one place and then you will not have to waste your time in searching for the
files.

Excel Worksheet

A worksheet is a collection of cells where you keep and manipulate the data. Each Excel
workbook can contain multiple worksheets. Each Excel file is a workbook that can hold
many worksheets. The worksheet is a grid of columns (designated by letters) and rows
(designated by numbers). The letters and numbers of the columns and rows (called labels) are
displayed in grey buttons across the top and left side of the worksheet. The intersection of a
column and a row is called a cell.

Yogesh Kumar 08417788819 B.Com-4B FM File

 Basics of a Spreadsheet

Spreadsheets are made up of:

Columns

COLUMN is defined as the vertical space that is going up and down the window. Letters are
used to designate each COLUMN'S location.

Column A is selected.

Rows

ROW is defined as the horizontal space that is going across the window. Numbers are used to
designate each ROW'S location.

Yogesh Kumar 08417788819 B.Com-4B FM File

Row 5 is selected.

Yogesh Kumar 08417788819 B.Com-4B FM File

Commonly used Shortcut Keys

Editing Shortcuts

Yogesh Kumar 08417788819 B.Com-4B FM File

Formatting Shortcuts

Yogesh Kumar 08417788819 B.Com-4B FM File

Navigation Shortcuts

Yogesh Kumar 08417788819 B.Com-4B FM File

Yogesh Kumar 08417788819 B.Com-4B FM File
Basic Commands of Microsoft Excel:

1.Wrap Text – It is a feature that wraps the text within a cell. For example, in the picture to
the right, cell G2 has text that has been cut off because of the adjacent cell H2 has text (the
number one), which is the default behaviour. However, cell G3 has the Wrap Text feature
enabled (found under the Home tab) that wraps the text in the cell, regardless of how much
text is in the cell. Wrap Text can be turned off by highlighting the cell and clicking the Wrap
Text button again.

2. Page Break-Page breaks are separators that divide a worksheet into individual pages for
printing. In Excel, page break marks are inserted automatically according to the paper size,
margin and scale options. We can easily insert page breaks in Excel manually. It helps in
printing a table with the exact number of pages you want.

3.Merge Cells- Merging cells are often used when a title is to be centred over a particular
section of a spreadsheet. When a group of cells is merged, only the text in the upper-leftmost
box is preserved.

TO MERGE A GROUP OF CELLS:

1. Highlight or select a range of cells.

Yogesh Kumar 08417788819 B.Com-4B FM File

 2. Right-click on the highlighted cells and select FormatCells....

3. Click the Alignment tab and place a checkmark in the checkbox labelled Merge cells.

To merge a group of cells and centre the text, you can also use the Merge and Centre button
on the Excel tool bar. Again, this will only preserve the text in the upper-leftmost cell.

1. Highlight or select a range of cells.

Click the Merge and Centre button on the toolbar.

4.Freeze Panes-If you have a large table of data in Excel, it can be useful to freeze rows or
columns. This way you can keep rows or columns visible while scrolling through the rest of
the worksheet.

Yogesh Kumar 08417788819 B.Com-4B FM File

5.Inserting and Deleting Rows &Columns-Excel makes it easy to insert and delete rows
and columns to deal with many kinds of changes.When you insert a new row or column, the
existing ones move to make room for it. You can insert multiple rows or columns at once by
selecting multiple ones before issuing the Insert command. (There’s no limit on the number
you can insert at once!) Similarly, you can delete multiple rows or columns by selecting them
before using the Delete command.

6.Fill Series-The Fill Handle in Excel allows you to automatically fill in a list of data
(numbers or text) in a row or column simply by dragging the handle.

7.Find & Replace-Find and Replace in Excel to search for specific data in a worksheet or
workbook.

Yogesh Kumar 08417788819 B.Com-4B FM File

The above picture shows the Find option in Find &Replace Dialog box.

The above picture shows the Replace option in Find & Replace Dialog box.

Yogesh Kumar 08417788819 B.Com-4B FM File

Basic Excel Functions

(a) Mathematical Functions

Sum Function

The syntax for SUM Function is: =SUM (number1, number 2).

Min Function:
The syntax for MIN Function is: = MIN (number1, [number 2].)

Yogesh Kumar 08417788819 B.Com-4B FM File

Average Function

The syntax for AVERAGE Function is: = AVERAGE(number1, number 2.

(b) Date Functions

Today Function

To enter today's date in Excel, use the TODAY function. To enter the current date and time, use the
NOW function. To enter the current date and time as a static value, use keyboard shortcuts.

E-date Function

Shift date and months in future or past

=EDATE (start date, months)

Yogesh Kumar 08417788819 B.Com-4B FM File

Start date - Start date as a valid Excel date.

months - Number of months before or after start date.

(c) Text Functions

Concatenate Function

The syntax for CONCATENATE Function is: =CONCATENATE(text1,text 2]..)

Yogesh Kumar 08417788819 B.Com-4B FM File

Trim Function

You use the TRIM function in Excel removes extra spaces from text. It deletes all leading,
trailing and in-between spaces except for a single space character between words.

Upper Function

The Microsoft Excel UPPER function allows you to convert text to all uppercase. The
UPPER function is a built-in function in Excel that is categorized as a String/Text Function.
It can be used as a worksheet function (WS) in Excel.

Yogesh Kumar 08417788819 B.Com-4B FM File

Proper Function

The Microsoft Excel PROPER function sets the first character in each word to uppercase and the rest
to lowercase. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the
PROPER function can be entered as part of a formula in a cell of a worksheet.

Charts and Graphs in Excel

Charts and graphs elevate your data by providing an easy-to-understand visualization of

numeric values.  While the terms are often used interchangeably, they are slightly different.
Graphs are the most basic way to represent data visually, and typically display data point
values over a duration of time. People often use charts and graphs in presentations to give
management, client, or team members a quick snapshot into progress or results.

Column chart 

Column charts are used to compare values across categories by using vertical bars.

To create a column chart, execute the following steps.

1. Select the range A1:A7, hold down CTRL, and select the range C1:D7.

Yogesh Kumar 08417788819 B.Com-4B FM File

2. On the Insert tab, in the Charts group, click the Column symbol.

1. Click Clustered Column.

Result:

Line Graph

To create a line chart, execute the following steps.

1. Select the range A1:D7.

2. On the Insert tab, in the Charts group, click the Line symbol.

3. Click Line with Markers.

Yogesh Kumar 08417788819 B.Com-4B FM File

Result:

Bar Chart

A bar chart is the horizontal version of a column chart. Use a bar chart if you have large text
labels.

To create a bar chart, execute the following steps.

1. Select the range A1:C6.

Yogesh Kumar 08417788819 B.Com-4B FM File

1. On the Insert tab, in the Charts group, click the Column symbol.

2. Click Clustered Bar.

Result:

Data series

A row or column of numbers that are plotted in a chart is called a data series. You can plot one
or more data series in a chart.

To create a column chart, execute the following steps.

1. Select the range A1:D7.

2. On the Insert tab, in the Charts group, click the Column symbol.

3. Click Clustered Column.

Result:

Yogesh Kumar 08417788819 B.Com-4B FM File

To launch the Select Data Source dialog box, execute the following steps.

1. Select the chart. Right click, and then click Select Data.

The Select Data Source dialog box appears.

2. You can find the three data series (Bears, Dolphins and Whales) on the left and the
horizontal axis labels (Jan, Feb, Mar, Apr, May and Jun) on the right.

Switch Row/Column

If you click Switch Row/Column, you'll have 6 data series (Jan, Feb, Mar, Apr, May and Jun)
and three horizontal axis labels (Bears, Dolphins and Whales).

Result:

Yogesh Kumar 08417788819 B.Com-4B FM File

Pie Chart

Pie charts are used to display the contribution of each value (slice) to a total (pie). Pie charts
always use one data series.

To create a pie chart of the 2017 data series, execute the following steps.

1. Select the range A1:D2.

2. On the Insert tab, in the Charts group, click the Pie symbol.

3. Click Pie.

Result:

Donut Pie Chart

Yogesh Kumar 08417788819 B.Com-4B FM File

1.Select the data and go to Insert Tab

2.Click on Chart

3.Select Pie Graph

4.Select the Donut Pie Chart

Area chart

An area chart is a line chart with the areas below the lines filled with colours. Use a stacked
area chart to display the contribution of each value to a total over time.

To create an area chart, execute the following steps.

1. Select the range A1:D21.

Yogesh Kumar 08417788819 B.Com-4B FM File

 2. On the Insert tab, in the Charts group, click the Line symbol. Click Area.

Market trends of Stocks of BSE

60.0000
50.0000
40.0000
Close 46.125
BSE Index

30.0000
chg
20.0000 Adva
10.0000
0.0000
-10.0000 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

Working hour

Stacked Bar Graph

1.Select the data and go to Insert Tab

2.Click on Chart

3.Select Bar Graph

4. Select the Stacked and 100% Stacked Bar Graph

Yogesh Kumar 08417788819 B.Com-4B FM File

Yogesh Kumar 08417788819 B.Com-4B FM File
Combo Chart

1.Select the data and go to Insert Tab.

2.Click on Chart.

3.Select Combo Graph.

4.Select stacked area and clustered column chart.

Yogesh Kumar 08417788819 B.Com-4B FM File

Conditional Formatting
Aim- Conditional formatting is a feature in many spreadsheet applications that allows you to
apply specific formatting to cells that meet certain criteria. It is most often used as colour-
based formatting to highlight, emphasize, or differentiate among data and information stored
in a spreadsheet.
Greater Than-

Yogesh Kumar 08417788819 B.Com-4B FM File

Top 10 items

Yogesh Kumar 08417788819 B.Com-4B FM File

Above Average

Yogesh Kumar 08417788819 B.Com-4B FM File

Data Bars

Yogesh Kumar 08417788819 B.Com-4B FM File

Color Scales

Icon Sets

Yogesh Kumar 08417788819 B.Com-4B FM File

 Sort Function

1. Highlight the rows and/or columns you want sorted.

2. Navigate to "Data" along the top and select "Sort."

3. If sorting by column, select the column you want to order your sheet by.

4. If sorting by row, click "Options" and select "Sort left to right."

5. Choose what you'd like sorted.

6. Choose how you'd like to order your sheet.

7. Click "OK."

EXAMPLE

Sort data by last name in alphabetical order.

RESULT

Yogesh Kumar 08417788819 B.Com-4B FM File

Filter Function

Basic

1. Select a cell or a range of cells.

2. Go to data. In sort and filter select filter.

3. Filter will be applied on the range of cells and it will be seen like this and it can be
changed as per the user’s requirement.

Advanced

When you use the Advanced Filter, you need to enter the criteria on the worksheet. Create
a Criteria range above your data set. Use the same column headers. Be sure there's at least
one blank row between your Criteria range and data set.

And Criteria

To display the sales in the USA and in Qtr. 4, execute the following steps.

1. Enter the criteria shown below on the worksheet.

2. Click any single cell inside the data set.

3. On the Data tab, in the Sort & Filter group, click Advanced.

4. Click in the Criteria range box and select the range A1:D2 (blue).

5. Click OK.

Yogesh Kumar 08417788819 B.Com-4B FM File

Result

Understanding Macros

A macro is a piece of programming code that runs in Excel environment and helps automate
routine tasks. A macro is a recording of your routine steps in Excel that you can replay using
a single button. Macros solve many problems by automating routine tasks. Macros are one of
developer features. By default, the tab for macros is not displayed in excel.

How to record a macro?

1. Navigate to the Developer tab and select the Record Macro button in the Code group OR
click the button in the bottom left corner of your screen that looks like a spreadsheet with a
red dot in the top left corner.
2. Create a name for your macro. Spaces are not allowed, but you can use an underscore
instead.
3. Select a shortcut key. Be sure to choose a letter not already being used as a shortcut key as
it will replace the original (i.e., don’t use Ctrl+Z unless you want to lose your shortcut for

Yogesh Kumar 08417788819 B.Com-4B FM File

 Undo). By using Shift, you can expand your options (i.e., you could use Ctrl+Shift+Z
instead).
4. Choose where to store your macro. Usually, you’ll want to store your macro in This
Workbook, which is what we’ve selected in our example. If you want it to be available
whenever you use Excel, select Personal Macro Workbook from the drop down menu.
5. Type a brief description about what your macro will do in the Description box.
6. Click OK.

Run a macro

Yogesh Kumar 08417788819 B.Com-4B FM File

Result

Yogesh Kumar 08417788819 B.Com-4B FM File

Advanced Excel Functions

What-if Analysis

What-If AnalysisinExcel allows you to try out different values (scenarios) for formulas. Assume you
own a book store and have 100 books in storage. You sell a certain % for the highest price of $50 and
a certain % for the lower price of $20.

(i) Creating Different Scenarios

Scenario manager in excel is a part of three what-if-analysis tools in excel, which are built-in in,
excel. In simple terms, you can see the impact of changing input values without changing the actual
data. Like Data Table in excel, you now input values that must change to achieve a specific goal.

Scenario Manager in Excel allows you to change or substitute input values for multiple cells
(maximum up to 32). Therefore, you can view the results of different input values or different
scenarios at the same time.

Step 1: Create a below table shows your list of expenses and income sources.

In cell B5, you have total income.

Yogesh Kumar 08417788819 B.Com-4B FM File

In cell B17, you have total expenses for the month.

In cell B19, total money left.

You are ending up with only 5,550 after all the expenses. So, you need to cut down your cost to save
more for the future…

Yogesh Kumar 08417788819 B.Com-4B FM File

Step 2: From the top of Excel, click the Data menu > On the Data menu, locate the Data
Tools panel > Click on the what-if-Analysis item and select the Scenario Manager in excel from the
menu.

Step 3: When you click on the Scenario Manager below, the dialogue box will open.

Step 4: You need to create a new scenario. So click on the Add button. Then you will get the below
dialogue box.

Yogesh Kumar 08417788819 B.Com-4B FM File

By default, it shows the cell C10, which means that it is the currently active cell. First, type the
Scenario Name in the box as the Actual Budget.

Now, you need to enter which cells your excel sheet will be changing. In this first scenario, nothing
will be changing because this is my actual budget for the month. Still, we need to specify the cells will
be changing.

Now try to reduce your Food expenses and Clothes expenses. These are in the cells B15 &
B13, respectively. Now your add scenario dialogue box should look like this.

Click, OK, and Excel will ask you for some values. Since we do not want any changes to this
scenario, just click OK.

Now, you will be taken back to the Scenario Manager Box. Now the window will look like this.

Yogesh Kumar 08417788819 B.Com-4B FM File

Now, one scenario is done and dusted. Create a second scenario, and this where you need to make
changes to your Food & Clothes expenses.

Click the Add button one more time and give a scenario name as “Plan 2”. Changing the cell will be
B15 & B13 (Food & Cloth expenses).

Now, below Scenario Values dialogue box opens again. This time, we do want to change the values.
Enter the same ones as in the image below:

These are the new values for our new scenario, Plan 2. Click OK, and now you are back to the
Scenario Manager window. Now we already have two scenarios named after Actual Budget & Plan 2.

Yogesh Kumar 08417788819 B.Com-4B FM File

Click the Add button one more time and give a scenario name as “Plan 3”. Changing the cell will be
B15 & B13 (Food & Cloth expenses).

Now, below Scenario Values dialogue box opens again. This time, we do want to change the values.
Enter the same ones as in the image below:

These are the new values for our new scenario, Plan 3. Click OK, and now you are back to the
Scenario Manager window. Now you have three scenarios named after Actual Budget, Plan 2, and
Plan 3.

Yogesh Kumar 08417788819 B.Com-4B FM File

As you can see, we have our Actual Budget, Plan 1 and Plan 2. With Plan 2 selected, click the Show
button at the bottom. The values in your excel sheet will change, and the new budget will be
calculated. The image below shows what it looks like.

Click on the Actual Budget, then click on the Show button to see the differences. Initial values will be
displayed.

Yogesh Kumar 08417788819 B.Com-4B FM File

Do the same for Plan 2 to look at the changes.

So Scenario Manager in Excel allows you to set different values and allows you to identify the
significant changes from them.

How to Create a Summary Report in Excel?

After we are done with adding different scenarios, we can create a summary report in excel from this
scenario manager in excel. To create a summary report in excel, follow the below steps.

Click on the Data tab from the Excel menu bar.

Click on What-If-Analysis.

Under the what-if-analysis, click Scenario Manager in Excel.

Now click on Summary.

Yogesh Kumar 08417788819 B.Com-4B FM File

Click ok to create the summary report in excel.

It will create the summary in the new sheet, as shown in the below image.

Yogesh Kumar 08417788819 B.Com-4B FM File

It shows the change in savings in three different scenarios. In the first scenario, the savings was 5,550.
In the second scenario, savings are increased to 20,550 due to cost cut down in Food & Clothes
section, and finally, the third scenario shows the other scenario.

All right, now we exercised a simple Family Budget Planner. It looks good enough to understand.
Perhaps this is enough to convince your family to change their lifestyle.

Scenario manager in Excel is a great tool when you need to do sensitivity analysis. You can create
the summary report in excel instantly to compare one plan with the other and decide the best
alternative plan to get a better outcome.

Yogesh Kumar 08417788819 B.Com-4B FM File

D ata T ab les

Instead of creating different scenarios, you can create a data table to quickly try out
different values for formulas. You can create a one variable data table or a two variable
data table.

Assume you own a book store and have 100 books in storage. You sell a certain % for
the highest price of $50 and a certain % for the lower price of $20. If you sell 60% for
the highest price, cell D10 below calculates a total profit of 60 * $50 + 40 * $20 =
$3800.

One Variable Data Table

To create a one variable data table, execute the following steps.

1. Select cell B12 and type =B6

2. Type the different percentages in column A.

3. Select the range A12:B17.

We are going to calculate the total profit if you sell 60% for the highest price, 70% for
the highest price, etc.

Yogesh Kumar 08417788819 B.Com-4B FM File

4. On the Data tab, in the Forecast group, click What-If Analysis. Click Data Table.

5. Click in the 'Column input cell' box (the percentages are in a column) and select cell
C4.

We select cell C4 because the percentages refer to cell C4 (% sold for the highest
price). Together with the formula in cell B12, Excel now knows that it should replace
cell C4 with 60% to calculate the total profit, replace cell C4 with 70% to calculate the
total profit, etc.

7. Click OK.

Result

Conclusion: if you sell 60% for the highest price, you obtain a total profit of $3800, if
you sell 70% for the highest price, you obtain a total profit of $4100, etc.

Yogesh Kumar 08417788819 B.Com-4B FM File

Two Variable Data Table

To create a two variable data table, execute the following steps.

1. Select cell A12 and type =D10 (refer to the total profit cell).

2. Type the different unit profits (highest price) in row 12.

3. Type the different percentages in column A.

4. Select the range A12:D17.

We are going to calculate the total profit for the different combinations of 'unit profit
(highest price)' and '% sold for the highest price'.

5. On the Data tab, in the Forecast group, click What-If Analysis.

6. Click Data Table.

7. Click in the 'Row input cell' box (the unit profits are in a row) and select cell D7.

8. Click in the 'Column input cell' box (the percentages are in a column) and select cell
C4.

Yogesh Kumar 08417788819 B.Com-4B FM File

9. Click OK.

Result

Yogesh Kumar 08417788819 B.Com-4B FM File

Goal Seek

1. On the Data tab, in the Data Tools group, click What-If Analysis, and then click Goal

Seek.

2. In the Set cell box, enter the reference for the cell that contains the formula that you
want to resolve. In the example, this reference is cell B4.

3. In the To value box, type the formula result that you want. In the example, this is -
900. Note that this number is negative because it represents a payment.

4. In the By changing cell box, enter the reference for the cell that contains the value that
you want to adjust. In the example, this reference is cell B3.
 

5. Click OK.

Goal Seek runs and produces a result, as shown in the following illustration.

6. Cells B1, B2, and B3 are the values for the loan amount, term length, and interest rate.

Cell B4 displays the result of the formula =PMT(B3/12,B2,B1).

Yogesh Kumar 08417788819 B.Com-4B FM File

Pivot Table and Pivot Charts

Pivot Table is a tool built into Excel that allows you to summarize large quantities of data
quickly and easily. A pivot table is a table of statistics that summarizes the data of a more
extensive table (such as from a database, spreadsheet, or business intelligence program). This
summary might include sums, averages, or other statistics, which the pivot table groups
together in a meaningful way.

Path: Insert> Tables> Pivot table

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.

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.

Yogesh Kumar 08417788819 B.Com-4B FM File

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.

Below you can find the pivot table. Bananas are our main export product. That's how easy pivot
tables can be!

Yogesh Kumar 08417788819 B.Com-4B FM File

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?

Yogesh Kumar 08417788819 B.Com-4B FM File

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.

Yogesh Kumar 08417788819 B.Com-4B FM File

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.

Yogesh Kumar 08417788819 B.Com-4B FM File

Below you can find the two-dimensional pivot table.

Pivot chart

A pictorial representation that is a visual representation of a Pivot Table.

Path: insert>charts>pivot chart

To insert a pivot chart, execute the following steps.

1. Click any cell inside the pivot table.

2. On the Analyse tab, in the Tools group, click PivotChart.

Yogesh Kumar 08417788819 B.Com-4B FM File

The Insert Chart dialog box appears.

3. Click OK.

Below you can find the pivot chart. This pivot chart will amaze and impress your boss.

Note: any changes you make to the pivot chart are immediately reflected in the pivot table
and vice versa.

Filter Pivot Chart

To filter this pivot chart, execute the following steps.

1. Use the standard filters (triangles next to Product and Country). For example, use the
Country filter to only show the total amount of each product exported to the United States.

Yogesh Kumar 08417788819 B.Com-4B FM File

2. Remove the Country filter.

3. Because we added the Category field to the Filters area, we can filter this pivot chart (and
pivot table) by Category. For example, use the Category filter to only show the vegetables
exported to each country.

Change Pivot Chart Type

You can change to a different type of pivot chart at any time.

1. Select the chart.

2. On the Design tab, in the Type group, click Change Chart Type.

3. Choose Pie.

Yogesh Kumar 08417788819 B.Com-4B FM File

4. Click OK.

Result:

Note: pie charts always use one data series (in this case, Beans). To get a pivot chart of a
country, swap the data over the axis. First, select the chart. Next, on the Design tab, in the
Data group, click Switch Row/Column.

DATA ANALYSIS TOOLPAK

Histogram

 Click on the File tab and then select the “Options”.

Yogesh Kumar 08417788819 B.Com-4B FM File

 Excel Options dialog box, select the Add-ins from the navigation pane.

 In the Add-ins dialog box, you have to select Analysis Toolpak and then click OK

 This will help you install the Analysis Toolpak. And then you can easily have the
access to it in the Data tab of the Analysis group.

Yogesh Kumar 08417788819 B.Com-4B FM File

In order to create a histogram while using this data, we need to create the data intervals from where
we would obtain the Data. The place from where you would want to find the data frequency. These
are called the bins.

 Visit the Data –> Go to Analysis –> Go to Data Analysis.

 In the Data Analysis dialog box, select the Histogram in Excel from the list.

 Click OK.

Yogesh Kumar 08417788819 B.Com-4B FM File

Pareto (sorted histogram)

A Pareto chart is a bar graph. The lengths of the bars represent frequency or cost (time or
money), and are arranged with longest bars on the left and the shortest to the right. In this
way the chart visually depicts which situations are more significant.

Chart output

Yogesh Kumar 08417788819 B.Com-4B FM File

Cumulative percentage

Yogesh Kumar 08417788819 B.Com-4B FM File

V-LOOKUP FUNCTION

You can use the VLOOKUP function to search the first column of a range of cells, and then
return a value from any cell on the same row of the range.

Purpose:The VLOOKUP function in Excel finds things in a table or range by row.

Syntax: =VLOOKUP(lookup value, table array, Col index num, [range lookup])

H-LOOKUP FUNCTION

Searches for a value in the top row of a table or an array of values, and then returns a value in
the same column from a row you specify in the table or array. Use HLOOKUP when your
comparison values are located in a row across the top of a table of data, and you want to look
down a specified number of rows. Use VLOOKUP when your comparison values are located
in a column to the left of the data you want to find.

The H in HLOOKUP stands for "Horizontal."

Purpose:

Yogesh Kumar 08417788819 B.Com-4B FM File

Searches for a value in the top row of a table or an array of values, and then returns a value in
the same column from a row you specify in the table or array.

Syntax:

=HLOOKUP(lookup value, table array, row index num, [range lookup])

PMT Function

PMT, one of the financial functions, calculates the payment for a loan based on constant payments
and a constant interest rate.

Syntax PMT(rate, nper, pv, [fv], [type])

The PMT function syntax has the following arguments:

 Rate    Required. The interest rate for the loan.

 Nper    Required. The total number of payments for the loan.

 Pv    Required. The present value, or the total amount that a series of future payments
is worth now; also known as the principal.

 Fv    Optional. The future value, or a cash balance you want to attain after the last
payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value
of a loan is 0.

 Type    Optional. The number 0 (zero) or 1 and indicates when payments are due.

To Find the monthly payment we will use the syntax, [=PMT(G2/12,)]

Yogesh Kumar 08417788819 B.Com-4B FM File

Yogesh Kumar 08417788819 B.Com-4B FM File
SOLVER

Solver tool in excel is quite a useful tool for those who are into the analysis domain which is used for
operations research to find the optimum solution for any kind of decision problem. Solver tool can
be activated in excel from Excel Options under the tab Add-Ins. We will be able to see this in the
Data tab under the analysis section as Solver. In Solver, we just need to add the cell formula or
problem need to solve, then select the cells which is affecting.

Where to find SOLVER in Excel?

Excel SOLVER tool is located under Data Tab > Analysis Pack > Solver.

If you are not able to see the SOLVER tool in your excel follow below steps to enable this option in
your excel.

Step 1: Firstly go to File and Options at the left-hand side of the excel.

Step 2: Select the Add-Ins after Options.

Yogesh Kumar 08417788819 B.Com-4B FM File

Step 3: At the bottom, you will see Excel Add-ins, select that, and click on Go…

Step 4: Select Solver Add-in and click OK.

Step 5: This will enable the SOLVER Add-in Option for you.

Yogesh Kumar 08417788819 B.Com-4B FM File

Yogesh Kumar 08417788819 B.Com-4B FM File
Yogesh Kumar 08417788819 B.Com-4B FM File
Yogesh Kumar 08417788819 B.Com-4B FM File
Yogesh Kumar 08417788819 B.Com-4B FM File
Yogesh Kumar 08417788819 B.Com-4B FM File
Data Validation

Data validation means checking the accuracy and quality of source data before using,
importing or otherwise processing data. Different types of validation can be performed
depending on destination constraints or objectives. Data validation is a form of data
cleansing. 

Create Data Validation Rule

To create the data validation rule, execute the following steps.

1. Select cell.

2. On the Data tab, in the Data Tools group, click Data Validation

3. In the Allow list, click Custom.

4. In the Formula, enter the criteria for validation.

Input Message
Input messages appear when the user selects the cell and tell the user what to enter.

Yogesh Kumar 08417788819 B.Com-4B FM File

On the Input Message tab:

1. Check 'Show input message when cell is selected'.

2. Enter a title.

3. Enter an input message.

Error Alert
If users ignore the input message and enter a number that is not valid, you can show them an error
alert.

Yogesh Kumar 08417788819 B.Com-4B FM File

On the Error Alert tab:

1. Check 'Show error alert after invalid data is entered.

2. Enter a title.

3. Enter an error message.

Building Templates

An Excel template is a report layout designed in Microsoft Excel for formatting your enterprise
reporting data in Excel spreadsheets.

Excel templates provide a set of special features for mapping data to worksheets and for performing
additional processing to control how the data is output to Excel workbooks.

If you create your own template, you can safely store it in the Templates folder. As a result, you can
create new workbooks based on this template without worrying that you overwrite the original file.

To create a template, execute the following steps.

1. Create a workbook.

2. On the File tab, click Save As.

3. Click Browse.

4. Enter a file name.

Yogesh Kumar 08417788819 B.Com-4B FM File

5. Select Excel Template (*.xltx) from the drop-down list.

Excel automatically activates the Templates folder. Notice the location of the Templates folder on
your computer.

It's usually located here: C:\Users\\Documents\Custom Office Templates

6. Click Save

Yogesh Kumar 08417788819 B.Com-4B FM File

Yogesh Kumar 08417788819 B.Com-4B FM File
Introduction to Financial Modelling
Financial modelling is the task of building an abstract representation (a model) of a real
world financial situation. This is a mathematical model designed to represent (a simplified version of)
the performance of a financial asset or portfolio of a business, project, or any other investment.

Typically, then, financial modelling is understood to mean an exercise in either asset pricing or
corporate finance, of a quantitative nature. It is about translating a set of hypotheses about the
behaviour of markets or agents into numerical predictions. At the same time, "financial modelling" is
a general term that means different things to different users; the reference usually relates either to
accounting and corporate finance applications or to quantitative finance applications.

The output of a financial model is used for decision making and performing financial
analysis, whether inside or outside of the company. Inside a company, executives will
use financial models to make decisions about:

a) Raising capital (debt and/or equity)

b) Making acquisitions (businesses and/or assets)

c) Growing the business organically (i.e. opening new stores, entering new
markets, etc.)

d) Selling or divesting assets and business units

e) Budgeting and forecasting (planning for the years ahead)

f) Capital allocation (priority of which projects to invest in)

Features of a Good Financial Model

1. Flexible

A good financial model will also be flexible. This is portrayed in both the design and
technique as it must allow the model to be flexible in both the immediate term and adaptable
in the longer term.

The model must have the ability to change with dynamic schedules – this grants model users
the ability to plug various numbers into cash flow projections, depreciation schedules, debt

Yogesh Kumar 08417788819 B.Com-4B FM File

service, inventory levels, the rate of inflation, etc. in order to run scenarios and make
modifications over an extended period by different analysts. A good financial model should
be able to be adjusted and manipulated in any field successfully. The key to flexibility is
simplicity.

2. Easy to follow and understand

Lastly, a model must be easy-to-follow. In order to really get the most out of a model for both
the analysts and the business owners, the entire model needs to be easy to read and follow. In
other words, the model needs to make the complex look simply.

3. Good assumptions

An effective and efficient model is one that can help a company see and reach their future
performance goals in various situations. This means that in order for a financial model to
function successfully, the quantitative values that are documented need to be realistic and
appropriate. The model should reflect key business assumptions directly without being over-
built or cluttered with unneeded details. This will ensure the model remains truthful and
represents reality, and that it is reasonable with previous data/ performance numbers,
defensible assumptions, and a projected performance.

Types of Financial Model

1) Three Statement Model

The 3 statement model is the most basic setup for financial modeling. As the name implies, in
this model the three statements (income statement, balance sheet, and cash flow) are all
dynamically linked with formulas. in Excel.  The objective is to set it up so all the accounts
are connected, and a set of assumptions can drive changes in the entire model.  It’s important
to know how to link the 3 financial statements, which requires a solid foundation of
accounting, finance and Excel skills. 

2) Initial Public Offering (IPO) Model

Investment bankers and corporate development professionals will also build IPO models in
Excel to value their business in advance of going public. These models involve looking

Yogesh Kumar 08417788819 B.Com-4B FM File

at comparable company analysis in conjunction with an assumption about how much
investors would be willing to pay for the company in question.  The valuation in an IPO
model includes “an IPO discount” to ensure the stock trades well in the secondary market.

3) Leveraged Buyout (LBO) Model

A leveraged buyout transaction typically requires modelling complicated debt schedules and

is an advanced form of financial modelling.  An LBO is often one of the most detailed and
challenging of all types of financial models as they many layers of financing create circular
references and require cash flow waterfalls.  These types of models are not very common
outside of private equity or investment banking.

4) Sum of the Parts Model

This type of model is built by taking several DCF models and adding them together.  Next,
any additional components of the business that might not be suitable for a DCF analysis
(i.e. marketable securities, which would be valued based on the market) are added to that
value of the business.  So, for example, you would sum up (hence “Sum of the Parts”) the
value of business unit A, business unit B, and investments C, minus liabilities D to arrive at
the Net Asset Value for the company.

5) Discounted Cash Flow (DCF) Model

The DCF model builds on the 3 statement model to value a company based on the Net
Present Value (NPV) of the business’ future cash flow. The DCF model takes the cash flows
from the 3 statement model, makes some adjustments where necessary, and then uses
the XNPV function in Excel to discount them back to today at the company’s Weighted
Average Cost of Capital (WACC).

6) Merger Model (M&A)

The M&A model is a more advanced model used to evaluate the pro forma accretion/dilution
of a merger or acquisition.  It’s common to use a single tab model for each company, where
the consolidation where Company A + Company B = Merged Co.  The level of complexity

Yogesh Kumar 08417788819 B.Com-4B FM File

can vary widely and is most commonly used in investment banking and/or corporate
development.

7) Consolidation Model

This type of model includes multiple business units added into one single model. Typically,
each business unit is its own tab, with consolidation tab that simply sums up the
other business units.  This is similar to a Sum of the Parts exercise where Division A and
Division B are added together and a new, consolidated worksheet is created. Check out CFI’s
free consolidation model template.

8) Budget Model

This is used to model finance for professionals in financial planning & analysis (FP&A) to
get the budget together for the coming year(s).  Budget models are typically designed to be
based on monthly or quarterly figures and focus heavily on the income statement.

Yogesh Kumar 08417788819 B.Com-4B FM File

Various approaches to valuation
3 Approaches to Valuing a Business
Business valuation professionals typically apply three approaches to valuing a business — the cost,
market and income approaches — ultimately relying on one or two depending on the type of case
and other factors. It’s vital that attorneys and clients who rely on business valuations understand the
basics of each approach.

1. Cost Approach

The cost (or asset-based) approach derives value from the combined fair market value (FMV) of the
business’s net assets. This technique usually produces a “control level” value, meaning the value to
an owner with the power to sell or liquidate the company’s assets. For that reason, a discount for
lack of control (DLOC) may be appropriate when using the cost approach to value a minority interest.
This approach is particularly useful when valuing holding companies, asset-intensive companies and
distressed entities that aren’t worth more than their net tangible value.

The cost approach includes the book value and adjusted net asset methods. The former calculates
value using the data in the company’s books. Its flaws include the failure to account for unrecorded
intangibles and its reliance on historical costs, rather than current FMV. The adjusted net asset
method converts book values to FMV and accounts for all intangibles and liabilities (recorded and
unrecorded).

2. Market Approach

The market approach bases the value of the subject business on sales of comparable businesses or
business interests. It’s especially useful when valuing public companies (or private companies large
enough to consider going public) because data on comparable public businesses is readily available.

Under this approach, the expert identifies recent, arm’s length transactions involving similar public
or private businesses and then develops pricing multiples. Several different methods are available,
including the:

Guideline public company method. This technique considers the market price of comparable (or
“guideline”) public company stocks. A pricing multiple is developed by dividing the comparable
stock’s price by an economic variable (for example, net income or operating cash flow).

Merger and acquisition (M&A) method. Here, the expert calculates pricing multiples based on real-
world transactions involving entire comparable companies or operating units that have been sold.
These pricing multiples are then applied to the subject company’s economic variables (for example,
net income or operating cash flow).

Under the market approach, the level of value that’s derived depends on whether the subject
company’s economic variables have been adjusted for discretionary items (such as expenses paid to
related parties). If the expert makes discretionary adjustments available to only controlling
shareholders, it may preclude the application of a control premium. If not, the preliminary value may
contain an implicit DLOC.

3. Income approach

When reliable market data is hard to find, the business valuation expert may turn to the income
approach. This approach converts future expected economic benefits — generally, cash flow — into

Yogesh Kumar 08417788819 B.Com-4B FM File

a present value. Because this approach bases value on the business’s ability to generate future
economic benefits, it’s generally best suited for established, profitable businesses.

The capitalization of earnings method capitalizes estimated future economic benefits using an
appropriate rate of return. The expert considers adjustments for such items as discretionary
expenses (for example, for above- or below-market owner’s compensation), nonrecurring revenue
and expenses, unusual tax issues or accounting methods, and differences in capital structure. This
method is most appropriate for companies with stable earnings or cash flow.

Sensitivity Analysis
A sensitivity analysis determines how different values of an independent variable affect a
particular dependent variable under a given set of assumptions. In other words, sensitivity
analyses study how various sources of uncertainty in a mathematical model contribute to the
model's overall uncertainty. This technique is used within specific boundaries that depend on
one or more input variables.

Sensitivity analysis is used in the business world and in the field of economics. It is
commonly used by financial analysts and economists, and is also known as a what-if analysis.

Example of Sensitivity Analysis

Assume Sue is a sales manager who wants to understand the impact of customer traffic on
total sales. She determines that sales are a function of price and transaction volume. The price
of a widget is $1,000, and Sue sold 100 last year for total sales of $100,000. Sue also
determines that a 10% increase in customer traffic increases transaction volume by 5%. This
allows her to build a financial model and sensitivity analysis around this equation based on
what-if statements. It can tell her what happens to sales if customer traffic increases by 10%,
50%, or 100%. Based on 100 transactions today, a 10%, 50%, or 100% increase in customer
traffic equates to an increase in transactions by 5%, 25%, or 50% respectively. The sensitivity
analysis demonstrates that sales are highly sensitive to changes in customer traffic.

Yogesh Kumar 08417788819 B.Com-4B FM File

Constructing the Matrix

1. In a cell on the worksheet, reference the formula that refers to the two input cells
that we would like to sensitize. In cell D208, we have referenced our EPS for
2009.

2. Type one list of input values in the same column, below the formula. In the
example, we have input a range of revenue growth assumptions.

3. Type the second list in the same row, to the right of the formula. In the example,
we have input a range of EBIT margin assumptions.

4. Select the range of cells that contains the formula and both the row and column of
values. In the example below, you would select the range D208:I214.

5. Hit the keys Alt-D-T on your keyboard. This will pull up the “Data Table” box as
shown to the right of the data table, below.Note: This “shortcut” works in both
Excel 2003 and 2007, although an alternative would be to hit Alt-A-W-T for the
2007 version, which will direct you to the data table box through the “What-If
Analysis” menu.

6. In the Row input cell box, enter the reference to the input cell for the input values
in the row. In the example below, you would type cell E35 in the Row input cell
box.

Yogesh Kumar 08417788819 B.Com-4B FM File

7. In the Column input cell box, enter the reference to the input cell for the input
values in the column. In the example below, you would type E33 in the Column
input cell box.

8. Click OK!

Yogesh Kumar 08417788819 B.Com-4B FM File

TIME VALUE OF MONEY

The time value of money (TVM) is the concept that money you have now is worth more than the
identical sum in the future due to its potential earning capacity. This core principle of finance holds
that provided money can earn interest, any amount of money is worth more the sooner it is
received. TVM is also sometimes referred to as present discounted value.

Loan Amortization using PMT Function

Problem:

Create a model to produce amortization table for a fixed rate loan, the loan is to repaid in
equal installment over its life and first payment is to be made at the end of first year. Use
Excel PMT Function to calculate required annual payment the model should validate input
for appropriateness and ask user to modify any input which is not appropriate.

Steps

1. To calculate Required annual payment type the syntax, [=PMT(rr,n,-P)]

2. Name the cells B4, B5, B6, B7 as rr, n, P, APmt respectively

3. To calculate the Annual Payment type the syntax, [=IF(C11>n,0,APmt)]

Yogesh Kumar 08417788819 B.Com-4B FM File

 4. To calculate the Interest Component use the syntax, [=IF(C11>n,0,D11*rr)]

5. To calculate the Principal Repaid use the syntax, [=IF(C11>n,0,E11-F11)]

6. To calculate the year end balance use the syntax, [=IF(C11>n,0,D11-E11)]

FV Formula or Future Value formula is used for calculating the future value of any loan
amount or investment. FV Formula returns the future value of any loan or investment
considering the fixed payment need to be done of each period, a rate of interest, and
investment or loan tenure.

The FVSCHEDULE function returns the future value of an initial principal after applying a
series of compound interest rates. Use FVSCHEDULE to calculate the future value of an
investment with a variable or adjustable rate.

Yogesh Kumar 08417788819 B.Com-4B FM File

PV is also known as the present value and this function is used to calculate the current
present value for any investment done and this present value is dependent on the rate of the
investment and the number of periods for the payment with the future value as an input, this
function is available in the financial category of the formulas tab in excel.

Yogesh Kumar 08417788819 B.Com-4B FM File

PMT, one of the financial functions, calculates the payment for a loan based on constant
payments and a constant interest rate.

PPMT returns the payment on the principal for a given period for an investment based on
periodic, constant payments and a constant interest rate.

IPMTReturns the interest payment for a given period for an investment based on periodic,
constant payments and a constant interest rate.

Yogesh Kumar 08417788819 B.Com-4B FM File

Ppmt andpmt for different years

Yogesh Kumar 08417788819 B.Com-4B FM File

NPER

The NPER function is categorized under Excel Financial functions. The function helps
calculate the number of periods that are required to pay off a loan or reach an investment goal
through regular periodic payments and at a fixed interest rate.

Yogesh Kumar 08417788819 B.Com-4B FM File

Rate

Returns the interest rate per period of an annuity. RATE is calculated by iteration and can
have zero or more solutions. If the successive results of RATE do not converge to within
0.0000001 after 20 iterations, RATE returns the #NUM! error value.

Effect

Returns the interest rate per period of an annuity. RATE is calculated by iteration and can
have zero or more solutions. If the successive results of RATE do not converge to within

Yogesh Kumar 08417788819 B.Com-4B FM File

0.0000001 after 20 iterations, RATE returns the #NUM! error value.

Nominal

Returns the nominal annual interest rate, given the effective rate and the number of
compounding periods per year.

Yogesh Kumar 08417788819 B.Com-4B FM File

Depreciation

Depreciation is a common accounting method that allocates the cost of a company's fixed
assets over the assets' useful life. In other words, it allows a portion of a company's cost of
fixed assets to be spread out over the periods in which the fixed assets helped generate
revenue.

Sln

The Microsoft Excel SLN function returns the depreciation of an asset for a period based on
the straight-line depreciation method.

Yogesh Kumar 08417788819 B.Com-4B FM File

DB

The Excel DB function returns the depreciation of an asset for a specified period using the
fixed-declining balance method. The calculation is based on initial asset cost, salvage value,
the number of periods over which the asset is depreciated and, optionally, the number of
months in the first year.

If function

The IF function runs a logical test and returns one value for a TRUE result, and another for a
FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than

Yogesh Kumar 08417788819 B.Com-4B FM File

one condition can be tested by nesting IF functions. The IF function can be combined with
logical functions like AND and OR to extend the logical test.

Yogesh Kumar 08417788819 B.Com-4B FM File

Financial statement

Income Statement

The Income Statement is one of a company’s core financial statements that shows their profit and
loss over a period of time.  The profit or loss is determined by taking all revenues and subtracting all
expenses from both operating and non-operating activities.

The income statement is one of three statements used in both corporate finance (including financial
modelling) and accounting. The statement displays the company’s revenue, costs, gross profit,
selling and administrative expenses, other expenses and income, taxes paid, and net profit, in a
coherent and logical manner.

Steps

1.Enter the data in Sales and Cost of sales.

2.To calculate the Gross Operating Income use the syntax, [B6-B7]

3.Enter the data in Selling Expenses, Depreciation, Other Net Expenses.

4.To calculate the EBIT, use the syntax [=B8-SUM(B10:B12)].

5.Enter values in Interest Income and Interest Expenses.

6.To calculate the Pre-tax profit, use the syntax, [=B13-SUM(B15:B16)]

7.Enter the values in Income Tax.

8.To calculate the Net Income type the syntax [=B17-B19]

9.Enter the value of Dividend.

10.To calculate the Additions to Retained Earnings, use the syntax [=B20-B22]

Yogesh Kumar 08417788819 B.Com-4B FM File

Income Statement (million)
Year Ending Dec.31

2013 2014 2015 2016

Sales 1234.9 1251.7 1300.4 1334.4
Cost of Sales 679.1 659 681.3 667
Gross Operating Income 555.8 592.7 619.1 667.4

Selling General & Admin.Expenses 339.7 348.6 351.2 373.3

Depreciation 47.5 52 55.9 75.2
Other net (Income)/ Expenses -11.8 -7.6 -7 -8.2
EBIT 180.4 199.7 219 227.1

Interest (Income) -1.3 -1.4 -1.7 -2

Interest Expense 16.2 15.1 20.5 23.7
Pre-Tax Income 165.5 186 200.2 205.4

Income Taxes 56.8 62.2 67.5 72.6

Net Income 108.7 121.8 132.7 132.8

Dividends 38.3 38.7 39.8 40.1

Additional to Retained Earnings 70.4 83.1 92.9 92.7
Balance Sheet

A balance sheet is a statement of the financial position of a business that lists the assets, liabilities,
and owner's equity at a particular point in time. In other words, the balance sheet illustrates your
business's net worth.

The balance sheet may also have details from previous years so you can do a back-to-back
comparison of two consecutive years. This data will help you track your performance and will
identify ways to build up your finances and see where you need to improve. 

You can also use the balance sheet to determine how to meet your financial obligations and figure
out the best ways to use credit to finance your operations. 

The balance sheet is the most important of the three main financial statements used to illustrate the
financial health of a business.

Steps

Yogesh Kumar 08417788819 B.Com-4B FM File

1. Enter the values for Cash & Marketable Securities, Accounts receivables, Inventories,
Other Current Assets.

2. To calculate the Total Current Assets use the syntax [=SUM(B27:B30)].

3. Enter the values of Property, Plant & Equipment Gross and Accumulated
Depreciation.

4. To calculate the value of Net Property, Plant & Equipment use the syntax [=B33-B34]

5. Enter the value of Other Non Current Assets.

6. To calculate the value of Total Non Current Assets use the syntax [=B35+B37]

7. To calculate Total Assets type the syntax [=B31+B38]

8. Enter the value of Accounts Payable, Short Term Debt, Other Current Liabilities.

9. To calculate the value of Total Current Liabilities use the syntax [=SUM(B43:B45)]

10. Enter the values of Long Term Debt, Deferred Income tax, Other Non Current
Liabilities.

11. To calculate the value of Total Liabilities use the syntax [=B46+SUM(B48:B50)]

12. Enter the value Paid in Capital, Retained Earnings.

13. To calculate the Total Shareholders Equity use the syntax [=B53+B54].

14. To calculate the Total Liabilities and Share Holders Equity use the syntax
[=B51+B55]

Yogesh Kumar 08417788819 B.Com-4B FM File

CASH FLOW

Year Ending Dec.31

Cash Flows from Operations 2013 2014 2015
Net Income 108.7 121.8 132.7
Depreciation Expense 47.5 52 55.9
Balance Sheet (million)
Decrease/(Increase) in Accounts Receivable -3.5 -4.4 -12.8
Decrease/(Increase) in Inventories Year Ending Dec.31
1.6 -6.9 -1.9
Assets 2013 2014 2015 2016
Decrease/(Increase) in Other Current Assets 5.3 -12.3 6.2
Cash and Marketable Securities 25.6 23 32.1 28.4
Increase/(Decrease) in Accounts Payable 5.7 5.3 -8.7
Accounts Receivable 99.4 102.9 107.3 120.1
108 28.2 114.9-48.3 116.860.8
Increase/(Decrease) in Other Current Liabilities
Inventory 109.6
Increase/(Decrease)
Other Current Assetsin Deferred Income Taxes 96.7 91.4 2.7 103.74.6 97.5 2.3
Total Current Assetsin Other Non-Current Liabilities
Increase/(Decrease) 331.3 325.320.5 358 -34.9 362.820.5

Total Cash Flows from Operations 216.7 76.9 255

Property,Plantand Equipment, Gross 680.9 734.3 820.8 913.1
Accumulated depreciation 244.8 296.8 352.7 427.9
Cash Flows from Investing
Property,Plantand Equipment,
(Additions to) Property, Net
Plant & Equipment 436.1 437.5-53.4 468.1-86.5 485.2-92.3
Other NonCurrent Assets 203.2 205.1 407 456.3
(Investment) in Other Non-Current Assets -1.9 -201.9 -49.3
Total Non Current Assets 639.3 642.6 875.1 941.5
Total Cash Flows from Investing -55.3 -288.4 -141.6

Total Assets 970.6 967.9 1233.1 1304.3

Cash Flows from Financing
Liabilities and Shareholders Equity
From Issuance/(Repayment) of Short-Term Debt -9.4 50.1 30.5
Account Payable 82.8 77.1 71.8 80.5
From Issuance/(Repayment) of Long-Term Debt -18.5 56.8 16.3
Short term Debts 39.1 29.7 79.8 110.3
123.8-0.8 172.1-7.9 111.36.6
From Sale/(Repurchase) of Equity
Other current Liability 152
Cash
Total Dividends Paid to Shareholders
Current Liabilities 273.9 230.6-38.7 323.7-39.8 302.1-40.1
Total Cash Flows from Financing -67.4 59.2 13.3
Long Term Debt 163.5 145 201.8 218.1
Deferred Income Taxes 22.3 19.6 15 12.7
Net Change in Cash & Marketable Securities 94 -152.3 126.7
Other Non Currents Liabilities 100.6 80.1 115 94.5
Total liabilities 560.3 475.3 655.5 627.4
Beginning Cash & Marketable Securities 25.6 23 32.1
Paid in Capital 46.9 46.1 38.2 44.8
Ending Cash & Marketable Securities 23 32.1 28.4
Retained Earnings 363.4 446.5 539.4 632.1
Net Change in Cash & Marketable Securities 94 -152.3 126.7
Total Shareholder Equity 410.3 492.6 577.6 676.9

Total Liability and Shareholders Equity 970.6 967.9 1233.1 1304.3

Other Data Yogesh Kumar 08417788819 B.Com-4B FM File

Stock Price(year end) 55.5 65.3 55.7 51.4
Average no. of share outstanding (millions) 48 47.3 46.8 46.2
Steps to prepare Cash Flow Statement

1.To find Net Income use the syntax [=Sheet1!C20]

2.To find Depreciation Expense use the syntax [=Sheet1!C11]

3.To calculate the Decrease/(Increase) in Accounts Receivable use the syntax [=Sheet1!B28-
Sheet1!C28]

4.To calculate the Decrease/(Increase) in Inventories use the syntax [=Sheet1!B29-Sheet1!

C29]

5.To find Decrease/(Increase) in Other Current Assets use the syntax [=Sheet1!B30-Sheet1!
C30]

6.To calculate the Increase/(Decrease) in Accounts Payable use the syntax [=Sheet1!C43-
Sheet1!B43]

7.To find Increase/(Decrease) in Other Current Liabilities use the syntax [=Sheet1!C45-
Sheet1!B45]

8.To find Increase/(Decrease) in Deferred Income Tax use the syntax [=Sheet1!C49-Sheet1!
B49]

9. To calculate Increase/(Decrease) in Other Non Current Liabilities use the syntax [=Sheet1!
C50-Sheet1!B50]

10.To find Total Cash Flows From Operating use the syntax [=SUM(C7:C15)]

11.To calculate the (Additions to) Property, Plant & Equipment use the syntax [=Sheet1!B33-
Sheet1!C33]

12.To find (Investment) in Other Non Current Assets use the syntax [=Sheet1!B37-Sheet1!
C37]

13.To find Total Cash Flows from Investing use the syntax [=SUM(C19:C20)]

14.To find From issuance/(Repayment) of Short Term Debt use the syntax [=Sheet1!C44-
Sheet1!B44]

Yogesh Kumar 08417788819 B.Com-4B FM File

15.To find From issuance/(Repayment) of Long Term Debt use the syntax [=Sheet1!C48-
Sheet1!B48]

16.To calculate From sale/(Repurchase) of Equity use the syntax [=Sheet1!C53-Sheet1!B53]

17.To find Cash Dividends paid to Shareholders use the syntax [=-Sheet1!C22]

18.To find Total Cash Flow from Financing use the syntax [=SUM(C24:C27)]

19.To find Net change in Cash & Marketable Securities use the syntax [=C16+C21+C28]

20.To calculate Beginning Cash & Marketable Securities use the syntax [=Sheet1!B27]

21.To find Ending Cash & Marketable Securities use the syntax [=Sheet1!C27]

22.To find Net change in Cash & Marketable Securities use the syntax [=C34-C33]

Yogesh Kumar 08417788819 B.Com-4B FM File

FINANCIAL RATIO

Value Ratios 2013 2014 2015 2016

EPS 2.3 2.6 2.8 2.9
Dividends per Share 0.8 0.8 0.9 0.9
P/E Ratio 24.5 25.4 19.6 17.9
P/B (price to book ) Ratio 6.5 6.3 4.5 3.5
DividendPayout Ratio 35% 32% 30% 30%
Profitability Ratio
Return on Equity (ROE) 27.0% 24.8% 21.2%
Return on Sales(ROS) 14.6% 16.0% 16.8% 17.0%

Growth Rate
EPS Growth Rate 13.7% 10.1% 1.4%
Dividends Growth Rate 2.5% 3.9% 2.1%
Sales Growth Rate 1.4% 3.9% 2.6%
EBIT Growth Rate 10.7% 9.7% 3.7%
Net Income Growth Rate 12.1% 8.9% 0.1%
Liquidity Ratio
Current Ratio 1.2 1.4 1.1 1.2
Quick Ratio 0.5 0.5 0.4 0.5
Operating Efficiency Ratio
Inventory Turnover Ratio 6.2 6.1 5.9 5.7
Receivable Turnover Ratio 12.4 12.2 12.1 11.1
Leverage Ratio
Total Debt to Total Capitalization 33.1% 26.2% 32.8% 32.7%
long Term Debt to Total Capitalization 26.7% 21.7% 23.5% 21.7%
Total debt to Equity 20.9% 18.0% 22.8% 25.2%
Coverage Ratio
Times Interest Earned (TIE) 11.1 13.2 10.7 9.6

Yogesh Kumar 08417788819 B.Com-4B FM File

Cash Coverage Ratio 1.4 1.4 1.4 1.5

Steps
1.To find EPS use the syntax [=B20/B62]
2.To find Dividend Per Share use the syntax [=B22/B62]
3.To calculate P/E Ratio use the syntax [=B61/B66]
4.To find P/B Ratio use the syntax [=B61/B55*B62]
5.To find Dividend Pay-out Ratio use the syntax [=B22/B20]
6.To find Return on Equity use the syntax [=C20/((B55+C55)/2)]
7.To find Return on Sales use the syntax [=B13/B6]
8.To calculate EPS Growth Rate use the syntax [=C66/B66-1]
9.To find Dividend Growth Rate use the syntax [=C67/B67-1]
10.To find Sales Growth Rate use the syntax [=C6/B6-1]
11.To find EBIT Growth Rate use the syntax [=C13/B13-1]
12.To find Net Income Growth Rate use the syntax [=C20/B20-1]
13.To find Current Ratio use the syntax [=B31/B46]
14.To find Quick Ratio use the syntax [=(B27+B28)/B46]
15.To find Inventory Turnover Ratio use the syntax [=B7/B29]
16.To calculate Receivable Turnover Ratio use the syntax [=B6/B28]
17.To find Total Debt to Total Capitalisation use the syntax [=(B44+B48)/(B44+B48+B55)]
18.To find Long Term Debt to Total Capitalisation use the syntax [=B48/(B44+B48+B55)]
19.To find Total Debt to Equity use the syntax [=(B44+B48)/B55]
20.To find Times Interest Earned use the syntax [=B13/B16]
21.To calculate the Cash Coverage Ratio use the syntax [=(B13+B11)/B16]

Yogesh Kumar 08417788819 B.Com-4B FM File

Future value of money

Future Value is the amount of money which will grow over a period of time with simple or
compounded interest. It is one of the most important concepts of finance and it is based on
the time value of money. Investors use this method to know what will be the future value of
their investment after a certain period of time calculates based on the assumed growth rate.
So future value basically tells us how much money you will get in any sort of investment in
the coming future.

Yogesh Kumar 08417788819 B.Com-4B FM File

We can calculate future value of different internals monthly, annually, quarterly

Cumulative Interest

Future Value is the amount of money which will grow over a period of time with simple or
compounded interest. It is one of the most important concepts of finance and it is based on
the time value of money. Investors use this method to know what will be the future value of
their investment after a certain period of time calculates based on the assumed growth rate.
So future value basically tells us how much money you will get in any sort of investment in
the coming future.

Yogesh Kumar 08417788819 B.Com-4B FM File

Ratio analysis

Ratio Analysis is a type of Financial Statement Analysis used to obtain a rapid indication of a
company’s financial performance in key areas.

You can use Ratio analysis to evaluate various aspects of a company’s operating and
financial performance like its efficiency, liquidity, profitability, and solvency, etc.

Yogesh Kumar 08417788819 B.Com-4B FM File

Yogesh Kumar 08417788819 B.Com-4B FM File
Capital budgeting

Yogesh Kumar 08417788819 B.Com-4B FM File

Capital budgeting is a set of techniques used to decide when to invest in projects. For
example, one would use capital budgeting techniques to analyse a proposed investment
in a new warehouse, production line, or computer system. There are a number of capital
budgeting techniques available, which include the following alternatives.

NPV

The NPV Function is an Excel Financial function that will calculate the Net Present Value
(NPV) for a series of cash flows and a given discount rate. It is important to understand
the Time Value of Money, which is a foundational building block of various Financial
Valuation methods.

XNPV

The XNPV function in Excel uses specific dates that correspond to each cash flow being
discounted in the series, whereas the regular NPV function automatically assumes all the time
periods are equal.  For this reason, the XNPV function is far more precise and should be used
instead of the regular NPV function.

Yogesh Kumar 08417788819 B.Com-4B FM File

IRR

The IRR function is categorized under Excel Financial functions. IRR will return the Internal
Rate of Return for a given cash flow, that is, the initial investment value and a series of net
income values.

MIRR

The Modified Internal Rate of Return (MIRR) is a function in Excel that takes into account
the financing cost (cost of capital) and a reinvestment rate for cash flows from a project or
company over the investment’s time horizon.

Yogesh Kumar 08417788819 B.Com-4B FM File

XIRR

The XIRR function is categorized under Excel financial functions. It will calculate
the Internal Rate of Return (IRR) for a series of cash flows that may not be periodic. It does
this by assigning specific dates to each individual cash flow. The main benefit of using the
XIRR Excel function is that such unevenly timed cash flows can be accurately modelled. To
learn more, read why to always use XIRR over IRR in Excel modelling.

PROFITABILITY INDEX

Yogesh Kumar 08417788819 B.Com-4B FM File

The profitability index (PI), alternatively referred to as value investment ratio (VIR) or profit
investment ratio (PIR), describes an index that represents the relationship between the costs
and benefits of a proposed project. It is calculated as the ratio between the present value of
future expected cash flows and the initial amount invested in the project. A higher PI means
that a project will be considered more attractive.

WACC
The weighted average cost of capital (WACC) is a financial metric that shows what the
total cost of capital (the interest rate paid on funds used for financing operations) is for
a firm. Rather than being dictated by a company's management, WACC is determined by
external market participants and signals the minimum return that a corporation would take in
on an existing asset base, in its effort to capture the interest of investors, creditors, and other
capital providers. Companies that don't demonstrate an inviting WACC number may lose
their funding sources, who are likely to deploy their capital elsewhere.

KEY TAKEAWAYS

 The weighted average cost of capital (WACC) is a financial metric that reveals what
the total cost of capital is for a firm. 
 The cost of capital is the interest rate paid on funds used for financing operations.
 Companies fund operations either through debt or equity, where each source has its
own associated cost.
 Companies without an inviting WACC number risk losing their funding sources, who
are likely to bring their dollars elsewhere.

Yogesh Kumar 08417788819 B.Com-4B FM File

WACC=wD× rD×(1−t)+wP× rP+ wE× rE

W = the
respective weight of debt,preferred stock/equity, and equityin the total 
capital structure.

t=tax rate

D=cost of debt

P=cost of preferred stock/equity

E=cost of equity

Yogesh Kumar 08417788819 B.Com-4B FM File

All companies must finance their operations, and this funding either comes from debt, equity,
or a combination of the two. Each source has a certain cost associated with it. And when
analysing different financing options, calculating the WACC provides the company with its
financing cost, which is then used to discount the project or business in a valuation model.

Cost of debt is the expected rate of return for the debt holder and is usually calculated as
the effective interest rate applicable to a firms liability. It is an integral part of the discounted
valuation analysis, which calculates the present value of a firm by discounting future cash
flows by the expected rate of return to its equity and debt holders.

The cost of equitycan be defined as the minimum rate of return required by the
shareholder or investor when equity is being put into the firm.

Cost of Equity RF+B(E(RM)_RF)

Cost of Debt (Rf+credit-spread)*(1-t)

Yogesh Kumar 08417788819 B.Com-4B FM File

Moving Average
Moving averages are a smoothing technique that looks at the underlying pattern of a set of data to
establish an estimate of future values. The most common types are the 2-month to 4-month moving
averages. 

Step1- Take the average of the above two months for two months moving average
Take the average of the above three months for three months moving average
Take the average of the above four months for four months moving average

Yogesh Kumar 08417788819 B.Com-4B FM File

Than insert a chart for the moving averages for the given months
Select the data – Data Analysis-Moving Average-Input

Yogesh Kumar 08417788819 B.Com-4B FM File

Calculate the Absolute Deviation
=ABS(number)

Yogesh Kumar 08417788819 B.Com-4B FM File

Calculate the Relative Deviation
=ABS ((revenue-2month moving average)/revenue)

Calculate the Mean Square Deviation

=SQRT(SUMXMY2(revenue,2month moving average)/COUNT(REVENUE))

Yogesh Kumar 08417788819 B.Com-4B FM File

TREND ANALYSIS
We can carry out a trend analysis in Excel when we want to know the percentage change of
events over a period of two or more years. Trend analysis is so crucial if you want to know
the underlying patterns in the past and current data movements. This will help you future
behavior of what you are studying. 
We can utilize the Excel TREND formula to find a linear trend line that passes through a set
of dependent valuables, Y and optionally through a set of independent variables, X.
SYNTAX:
=TREND (known_y’s, [known_x’s], [new_x’s], [const])
Things to Remember
1. The existing historic data that contains the known values of X and Y needs to be
linear data that for the given values of X the value of Y should fit the linear curve
y=m*x + c, otherwise the output or the predicted values may be inaccurate.
2. The TREND function in excel generates #VALUE! Error when the given known
values of X or Y is non-numeric or the value of new X is non-numeric and also when
the const argument is not a Boolean value (that is TRUE or FALSE)
3. The TREND function in excel generates #REF! Error known values of X and Y are of
different lengths.
QUESTION:
In this example, suppose we have data for test scores with their GPA, now using this given
data we need to make the prediction for the GPA. We have the existing data in column A and
B, the existing values of GPA corresponding to scores is the known values of Y and the
existing values of the score are the known values of X. We have given with some values for

Yogesh Kumar 08417788819 B.Com-4B FM File

X values as a score and we need to predict the Y values that is the GPA based on the existing
values.

QUESTION:
So in this Example, we have existing sales data of a company for the year 2017 that increases
linearly from Jan 2017 to Dec 2017. We need to figure out the sales for the given upcoming
months that is we need to predict the sales values based on the predictive values for the last
one year data.
The existing data contains the dates in column A and the sales revenue in column B, we need
to calculate the estimated sales value for next 5 months. Historical Data is given below:

Yogesh Kumar 08417788819 B.Com-4B FM File

EXPONENTIAL SMOOTHING
Exponential smoothing is a broadly accurate principle for smoothing time series data using the
exponential window function. The controlling input of the exponential smoothing calculation is
defined as the smoothing factor or the smoothing constant.
Exponential Smoothing in Excel is an inbuilt smoothing method used for Forecasting,
Smoothing the data, trend projection. To access, Exponential Smoothing in Excel, go to the
Data menu tab and, from the Data Analysis option, choose Exponential Smoothing.

 Go to the Data tab. Click on the Data Analysis option under the Analysis section,

Yogesh Kumar 08417788819 B.Com-4B FM File

 It will open a dialog box Data Analysis options.

 Click on the Exponential Smoothing option from the list of options and then click

on OK as shown below.

 A dialog box appears for the Exponential Smoothing method.

 Under the Input Range box select, the Price values range

 In the Damping Factor box, enter the value 0.9. This 0.9 is called the damping factor,

which is equal to the 1- α. Here α (alpha) is the smoothing factor.

Yogesh Kumar 08417788819 B.Com-4B FM File

 Under the Output Range box, select the cell where you want to see the result.

 Tick on Chart Output box for displaying the values in the chart and then click on OK.

 It will insert the damping values in the E column with the Exponential Smoothing

chart

Yogesh Kumar 08417788819 B.Com-4B FM File

Let’s consider α=0. and α=0.8 for the above-given data values so enter the value 0.8 in the

Damping Factor box and again repeat the Exponential Smoothing method.

SCENARIO ANALYSIS
Scenario analysis is a process of examining and evaluating possible future events. It
considers various feasible results or outcomes.
In financial modelling, this process is typically used to estimate changes in the value of a
business or cash flow. Especially, when there are potentially favourable and unfavourable
events that could impact the company.

Yogesh Kumar 08417788819 B.Com-4B FM File

1. Calculate projected scenarios for revenue growth (2020 – 2023) using assumptions for
2019 and the step.

Note: Don’t forget to anchor column K for the step, so you can fill the formula right and down.

After completing the formula in D13, highlight D13– J13 with Shift + Right / Down Arrow (shortcut to
highlight cells in Excel). Then, fill the formula to the right (Ctrl + R shortcut) and down (Ctrl + D
shortcut).

Yogesh Kumar 08417788819 B.Com-4B FM File

2. Based on the Scenarios for Revenue Growth let’s build Scenarios for Revenue.

Note: Don’t forget to anchor column K for the step, so you can fill the formula right and down.

After completing the formula in H14, highlight G19 – J19 with Shift + Right / Down Arrow (shortcut to
highlight cells in Excel). Then, fill the formula to the right (Ctrl + R shortcut) and down (Ctrl + D
shortcut).

Yogesh Kumar 08417788819 B.Com-4B FM File

 4. Let’s build a Scenario switch (cell H23 in our example) and use the CHOOSE
function to build scenarios.

The Excel CHOOSE function returns a value from a list using a given position or index.

CHOOSE(index_num,value1,[value2],…)
– If index_num is 1, CHOOSE returns value1;
– If it is 2, CHOOSE returns value2; and so on.

Yogesh Kumar 08417788819 B.Com-4B FM File

See on the next screenshots how different scenarios reflect in H23 thanks to the CHOOSE
function we have used.

Yogesh Kumar 08417788819 B.Com-4B FM File

4. Let’s use INDEX function to build scenarios
The Excel INDEX function returns the value at a given position in a range or array.
INDEX(array,row_num,[column_num]).
Array – Array of cells,
Rows – It selects the row in the array from which to return a value,
Column_num – It selects the column in the array from which to return a value.

5. Let’s use OFFSET function to build scenarios.

The OFFSET function is one of the built-in functions in Microsoft Excel. Its purpose is to return a
range that is a specified number of rows and columns from a reference cell or range. The range that
the OFFSET function returns can be a single cell or a range of multiple adjacent cells.

Yogesh Kumar 08417788819 B.Com-4B FM File

OFFSET(reference,rows,cols).
Reference – Initial Cell,
Rows – How Many Rows Down From The Initial Cell,
Cols – How Many Columns To The Right From The Initial Cell.

See on the next screenshots how different scenarios reflect in H23 thanks (CHOOSE function), H25
(INDEX function) and H27 (OFFSET function).

Yogesh Kumar 08417788819 B.Com-4B FM File

Yogesh Kumar 08417788819 B.Com-4B FM File