Farhan Ansari 03525588819( Financial Modelling-210)Lab Manual PDF

Preview

Summary

Download Farhan Ansari 03525588819( Financial Modelling-210)Lab Manual PDF

Description

SUBMITTED IN PARTIAL FULFILLMENT OF REQUIREMENT OF BACHELOR OF COMMERCE (HONOURS)

Guru Gobind Singh Indraprastha University LAB MANUAL OF FINANCIAL MODELLING

B. Com (H)-210

JIMS Engineering Management Technical Campus Greater Noida

Submitted to:

Submitted by:

DR. Shalini Singh

Farhan Ansari

Asst. Professor

Enrolment No.- 03525588819

Department of Commerce

B.COM IV Semester

STUDENT DECLARATION I hereby declare that the project report entitled FINANCIAL MODELING LAB MANUAL submitted to DR. SHALINI SINGH, is a record of an original work done by me under the guidance of DR. SHALINI SINGH, assistant professor at department of commerce of JAGANNATH INSTITUTE OF MANAGEMENT, ENGINEERING & TECHNOLOGY. I further declare that the work reported in this project has been fully completed by me and altered by my professor DR. SHALINI SINGH.

Name: Farhan Ansari Course: B.COM(H) Enrollment No.: 03525588819

CERTIFICATE This is to certify that Farhan Ansari, a student of B.COM department has successfully completed the lab work of Financial Modelling Lab Manual under the guidance of DR. SHALINI SINGH (Assistant professor of B.com department) during the year 2020-2021 is partial fulfillment of Financial Modelling examination.

DR. SHALINI SINGH (Asst. Professor Department of Commerce)

ACKNOWLEDGEMENT I would like to express my special thanks of gratitude to my professor ‘DR. SHALINI SINGH’ for their able guidance and support in completing my project. I would also like to extend my gratitude to Head of department Dr. Sumit Agarwal and Class coordinator Ms. Bhavana Sharma for providing me with all facilities that was required.

Farhan Ansari B COM. (H) Enrollment no – 03525588819

TABLE OF CONTENT S.No. 1

2

3 4 5

6

7

8

9

10

11

12

Topics Financial Modelling Introduction Importance Components MS Excel Features Screenshot of excel describing the tabs Cell Reference Data Formats Conditional Formatting-Introduction Types of Conditional Formatting How to apply Conditional Formatting Screen Shots after application Macros Introduction How to enable it How to run it Screens shots for every step Template Introduction Features Application with Example What if Analysis Scenario Manager Goal Seek Data Table Pivot Table Introduction Create a pivot table Steps to create pivot table Screen Shots after application Lookup Function Introduction Application with Example VLOOKUP-Purpose, Syntax and application (Screenshots) Steps of VLOOKUP HLOOKUP-Purpose, Syntax and application (Screenshots) Steps of HLOOKUP Index & Matching Purpose, Syntax and application (Screenshots) Various Text Functions (Left, Right, find, Proper, Trim, Rept, Upper, Lower, Concatenate) Purpose, Syntax and application (Screenshots)

Page No. 1

2

3 4-5 6-10

11-15

16-20

21-23

24-25

26-31

30-32

33-35

13

14 15 16 17 18

19

20

21

22

23

24

25

Count Function: Introduction, Purpose, Syntax and application (Screenshots) Count 36-37 Count A Count Blank Count if and Count ifs Statistical Functions: Purpose, Syntax and application (Screenshots) 38-41 Logical Functions (IF, OR, AND): Purpose, Syntax and 42-43 application (Screenshots) Historical Data 44-47 Trend & Forecast 48-56 Purpose, Syntax and application (Screenshots) Time Value of Money 57-61 What is TVM Significance of TVM Various Terminologies Used Application with Example Capital Budgeting 62-75 Introduction Significance Theories of Capital Budgeting Application of each individually Examples with screenshots Cost of Capital 76-80 Introduction Significance Types of Cost & their application Examples with screenshots WACC Forecasting Methods 81-82 Moving Average: Introduction, Significance & Application with example Exponential: Introduction, Significance & Application with example Ratio Analysis 83-86 Introduction Significance Various Ratios calculated with examples Earnings per share 87 Introduction Calculation with example Valuation Method 88-89 Discounted cash flow modelling Comparable trading multiples Precedent Transact Market Based Methods EV EBIT EBITDA EV/EBITDA

90-96

26

27

EV/Sales Sensitivity Analysis Introduction Application with Example Probability Analysis Introduction Application with Example

97

98

1. FINANCIAL MODELING INTRODUCTION Financial modeling is the task of building an abstract representation (a model) of a realworld 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 modeling 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 behavior of markets or agents into numerical predictions.[2] At the same time, "financial modeling" 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 application IMPORTANCE Financial Modelling is the main core element to take the major business decisions in a corporate world. Financial models are the most valuable tools for executing business choices to get perfect solutions. A model can advise you regarding the grade of risk associated with implementing certain decisions. They can also be utilized to devise an effective financial statement that reflects the finances and operations of company. These models help online internet businesses take quick decisions more confidently.

COMPONENTS • • • • • • • •

Assumptions and drivers. Income statement. Balance sheet. Cash flow statement. Supporting schedules. Valuation. Sensitivity analysis. Charts and graph

1

2. MS EXCEL FEATURES • • • • • • • •

Add Header and Footer. MS Excel allows us to keep the header and footer in our spreadsheet document. Find and Replace Command. ... Password Protection. ... Data Filtering. ... Data Sorting. ... Built-in formulae. ... Create different charts (Pivot Table Report) ... Automatically edits the result

SCREENSHOT OF EXCEL DESCIBING THE TAB

2

3. CELL REFERNCE A cell reference refers to a cell or a range of cells on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values or data that you want that formula to calculate. In one or several formulas, you can use a cell reference to refer to: ... Data on other worksheets in the same workbook.

3

4. DATA FORMATS Data appears in different sizes and shapes, it can be numerical data, text, multimedia, research data, or a few other types of data. The data format is said to be a kind of format which is used for coding the data. The data is coded in different ways. It is being coded, so that it can be read, recognized, and used by the different applications and programs. In the information technology, the data format may be referred in different ways. It can be termed as the data type, which is a constraint in the type system which was positioned after the interpretation of the data. It is also termed as a file format which is being used for storing the encoding data in a computer file. Or it can also be termed as Content Format, where the media data is represented in the particular format, that is a video format and audio format. When it comes to choosing a data format, there are several things which one need to check like the characteristics of the data or the size of the data, infrastructure of the projects, and the use case scenarios. Certain tests are performed in order to choose the right data format by checking the speed of writing and reading the data file. Mainly there are three main types of data formats which are also called as GIS Data formats. All of these data formats are handled in a different way. They are being used for different purposes. The three data formats are: • • •

File-Based Data Format Directory-Based Data Format Database Connections Below, we have explained these three types of data formats: File-Based Data Format – This type of data format includes either one file or more than one file. These files are then stored in any of the arbitrary folders. In most of the cases, it uses the single file only for example DGN. But then there are cases, which even includes at least three files. The filename extension of all these three files is different from each other. That is SHX, SHP, and DBF. All three files are important and are required here. As different tasks are performed by all these three files internally. One uses the filename as the name of the data source. There are many layers present in it, and it is not possible to know about them just with the help of the filename. Like in shapefile, there is only one data source for every shapefile. And there is only one layer, which is named similarly as the name of the file. Some of the examples of file-based data format are MicroStation Design Files, Shapefiles, and GeoTIFF images. Directory-Based Data Format – In this type of data format, whether there is one file or there is more than one file, they are all stored in the parent folder in a particular manner. There are some cases where the requirement of an additional folder is there in the file tree in some other location so that it can be accessed easily. It is a possibility that data source is the directory itself. There are many files present in the directory, which are represented at the available data’s layers. For example, the Polygon Data is represented by the PAL.ADF. As there is more than one file in the folder with the ADF file extension which is included in the ESRI ArcInfo Coverages. The ADF file extension includes the line string data or the arc string data. All the ADF files serve as a layer which is present in the data source inside the folder. Some of the 4

examples of Directory-Based Data Format are US Census TIGER and ESRI ArcInfo Coverages. Database Connections – In one respect, the database connections are quite similar to the above-mentioned data formats that are file and directory-based data format. For interpreting, for Map Server, they give geographic coordinate data. One need to access the coordinates inside the Map Server, that are creating the vector datasets. The stream of coordinates that are provided by the database connections is stored temporarily in the memory. The MapServer then reads these coordinates for making the map. Coordinate Data is the most important part and most of the focus is on it only. However, one may also require tabular data and attributes. The database connection generally consists of the following information like Host that is the server’s direction, Database Name, the Username and Passwords, Geographic Column name, and the table name or the view name. A few examples of Database Connections are MySQL, ESRI, PostGIS, and ArcSDE.

5

5. CONDITIONAL FORMATTING Conditional formatting is a feature in many spread sheet applications that allows you to apply specific formatting to cells that meet certain criteria. It is most often used as colourbased formatting to highlight, emphasize, or differentiate among data and information stored in a spread sheet. Conditional formatting enables spread sheet users to do a number of things. First and foremost, it calls attention to important data points such as deadlines, at-risk tasks, or

budget items. It can also make large data sets more digestible by breaking up the wall of numbers with a visual organizational component. Finally, conditional formatting can transform your spread sheet (that previously only stored data) into a dependable “alert” system that highlights key information and keeps you on top of your workload. TYPES OF CONDITIONAL FORMATTING • • •

• •

Background Color Shading (of cells) Foreground Color Shading (of fonts) Data Bars. Icons (which have 4 different image types) Values.

FEATURES OF CONDITIONAL FORMATTING Use conditional formatting to help you visually explore and analyse data, detect critical issues, and identify patterns and trends. Conditional formatting makes it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, color scales, and icon sets that correspond to specific variations in the data.

A conditional format changes the appearance of cells on the basis of conditions that you specify. If the conditions are true, the cell range is formatted: if the conditions are false, the cell range is not formatted. There are many built-in conditions, and you can also create your own (including by using a formula that evaluates to True or False).

For Example:

This is the data in which we will perform conditional formatting

6

7

HOW TO APPLY CNDITIONAL FORMATTING STEPS TO CONDITIONAL FORMATTING

o o o o o o

Select the range A1:A10. On the Home tab, in the Styles group, click Conditional Formatting. Click Highlight Cells Rules, Greater Than. Enter the value 80 and select a formatting style. Click OK. Result. Excel highlights the cells that are greater than 80. Change the value of cell A1 to 81.

INTERPRETATION Quickly Identify Duplicates Conditional formatting in Excel can be used to identify duplicates in a dataset. •

Select the dataset in which you want to highlight duplicates

Go to Home –> Conditional Formatting –> Highlighting Cell Rules –> Duplicate Values.

8

3. In the Duplicate Values dialogue box, make sure Duplicate is selected in the left drop down. You can specify the format to be applied by using the right drop down. There are some existing formats that you can use, or specify your own format using the Custom Format option.

4.

Click ok

9

You can apply conditional formatting to a range of cells (either a selection or a named range), an Excel table, and in Excel for Windows, even a PivotTable report. This is why conditional formatting is a really helpful tool.

CONCLUSION Conditional formatting enables you to apply special formatting to cells in your spreadsheet that meet certain criteria. Use conditional formatting to help you visually explore and analyse data, detect critical issues, and identify patterns and trends. Conditional formatting makes it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, color scales, and icon sets that correspond to specific variations in the data.

10

6. MACROS INTRODUCTION Macro is a record and playback tool that simply records your Excel steps and the macro will play it back as many times as you want. Macros save time as they automate repetitive tasks. PURPOSE OF MACRO A macro is used to automate a task that you perform repeatedly or on a regular basis. It is a series of commands and actions that can be stored and run whenever you need to perform the task. You can record or build a macro and then run it to automatically repeat that series of steps or actions. HOW TO ENABLE IT & HOW RUN IT EXAMPLE OF RECORDING MACROS IN EXCEL we will work with the following data to write macros in Excel.

INTERPRETATION Firstly we have to enable the ‘DEVELOPER’ option STEP 1:Go to main menu ‘FILE’ and select ‘OPTIONS’

11

STEP 2:Now another window will open, in that window do following things • • •

Click on Customize Ribbon Mark the checker box for Developer option Click on OK button

12

STEP 3: Developer Tab

STEP 4: Record Macro 1. Click on the DEVELOPER tab 2. Click on Record Macro as shown in the image below

You will get the following dialogue window

1. We will enter ‘PROGRESS_REPORT’ as macro name 2. Then we will enter the shortcut key but it should not be similar to the shortcut keys of MS Excel 3. Click ‘OK’ STEP 5: Perform Macro Operations/Steps you want to record •

We will first insert the rows for subjects

13

•

Add the sum and use the SUM function to get the total amount for all months.

STEP 6: Stop recording macro

Now that we have finished our routine work, we can click on stop recording macro button

14

STEP 7: Replay the Macro

• • • •

Click on developer tab Click Macros Select the macro name you have made Click ‘RUN’

You will get the following data for all the remaining students

CONCLUSION Macro is an action or a set of actions that you can record, give a name, save and run as many times as you want and whenever you want. When you create a macro, you are recording through your mouse clicks and keystrokes. When you run a saved macro, Macros help you to save time on repetitive tasks involved in data manipulation and data reports that are required to be done frequently. 15

7. TEMPLATE INTRODUCTION Templates allow you to create blank sheets and blank workbooks that have customized formats as well as customized Page Setup settings, including headers and footers. A template is a special Excel file type that when opened through Excel, creates a normal sheet or a normal file based on the template file settings. If you open a template file directly from a folder it will open as a template file and allow you to edit it and save it. THERE ARE THREE TYPES OF TEMPLATE FILES • • •

DEFAULT SHEET TEMPLATE– used when creating a new blank sheet DEFAULT WORKBOOK TEMPLATE– used when creating a new blank workbook CUSTOM WORKBOOK TEMPLATE– used to create data entry spreadsheet for things like travel re-imbursements or capital expenditure submissions.

Excel comes with a number of templates already installed. You can also download free and paid templates from the internet. Templates are especially valuable for frequently used document types such as Excel calendars, budget planners, invoices, inventories and dashboards. Instead of starting with a blank sheet, you can quickly create a new workbook based on an Excel template. The right template can really simplify your life since it makes the most of tricky formulas, sophisticated styles and other features of Microsoft Excel FEATURES • •

•

• •

•

Templates increase flexibility: Templates actually increase flexibility, they’re easy to update, and they provide consistency across the project. They are easy to use: Having access to a library of templates gives you the flexibility of being able to choose from a diverse set of preconfigured interactions, without needing to program o configure anything. Reduce rework time: Having templates ready to go means that you don’t have to waste time building or creating courses from scratch. Just use the templates you need according to your learning needs, and use them over and over again! Let you focus on what’s really important: Another huge plus is that you can now spend time focusing on the learning content and less on how to make a button or slide look good. Reduce error by exhaustive technical tests: before a template is released to the production process, it goes through several tests in different platforms and conditions in order to eliminate programming bugs. Speed-up development time: Templates allow organizations to produce courses in a small fraction of the time it would take to do traditionally Template...