Excel Layout & Settings

Basics are key to everything. So, in this first episode of our masterclass let’s start from the very beginning of Microsoft Excel. Understanding what a workbook is, spreadsheet, cells, and more is important to build the fundamentals. Let’s begin now!

Layout Overview

Excel has some tabs that will be frequently used for operations. Part 1 covers Home tab, Insert Tab, Draw, and Page Layout. Let’s dive in now.

Main Tabs - I

Shortcuts are quite helpful because they expedite your work. Financial analysts are quite skilled at using shortcuts, and we believe that this course will help you become equally proficient. Tabs like Formula, Review, View, and Data are covered in Part 2 of the Main Tabs. Now let's begin this lesson!

Main Tabs - II

Making the most of Excel requires optimizing your worksheet settings. Now let's study how to customize file settings to suit our needs. 

General Workbook Settings

Excel Layout & Settings Workbook

What You Will Learn

Basics to Advanced MS-Excel Course

Anyone interested in learning how to utilize excel in the most efficient way is encouraged to enroll in this course. It is not necessary to have any prior financial, stock market, or research knowledge. The only requirement is your desire to learn!

INTRODUCTION

This masterclass has been curated by a former Financial analyst with an experience of working closely with private equity firms and asset management companies to present career-advancing knowledge in the most simple way possible so you can remain ahead of the curve! 

 Basic Financial Setup

This session will help you create a basic income statement using the inputs that are provided along with helping you learn the color codes followed in the industry. So what are you waiting for? Start creating.

Basic Financial Setup 

Do you think making forecasts is a tedious task? Now that you have access to these shortcuts, you can forecast quickly!  So, now, forecast income statement growth figures utilising several shortcuts. Start learning now!

Forecasting

Basic Financial Setup Workbook

Basic Functions

It's crucial to understand what an Excel function is aiming to do before you start applying it. Once you understand the terms associated with the function, its syntax, and its arguments, you will have the necessary logic to utilize it wherever needed. So, let's start with this lesson now!

Structure of a Function

The most fundamental Excel functions, such as Sum, Count, and Average and more will be covered in the first lesson of this masterclass. Begin this session right away.

Basic Functions - I

In the previous session we explored the basic excel functions. Here, we'll revisit those functions with a twist! We will now attach specific conditions to them. Let's examine these criteria and the contexts in which they should be used.

Basic Functions - II

In this session, we will discover a new function called an ‘IF’ function. However, IF function in itself has limitations, so we will also use other functions with it to better our analysis by nesting other functions inside of IF. Check it out right away!

IF & Related functions

When you're making your shopping list and you need to know how much your total will be, you won't do it by adding up the cost of each order separately. Instead, make your process simpler by using Excel. This next function will assist you in doing so! So, let's check it out now!

SumProduct

When you're first starting out with your analysis, it is very important to clean up your data. These Text functions will help you not only clean your data, but extract the required information from the text strings. Not just that, the Date functions will further make things easier for you. So, let's begin with the session, without much adieu!

Text, Date, & Concatenate Functions

Basic Function Workbook

Advanced Functions

In our earlier classes, we covered the fundamentals; now it's time to advance. During a job interview, one of the most important questions is about the lookup functions, precisely the Vlookup and Hlookup as they're essential for locating values in a large dataset. So, start this lesson now. 

Vlookup & Hlookup

Nesting Match inside of Index overcomes the limitations of Vlookup & Hlookup. It is a two-way look up operation to get insights out of a complex dataset. Don’t worry it is super easy! Begin this lesson right away.

Index & Match

Are you looking to make your analysis dynamic? Well, we’ve got your back. This function will help you integrate Hlookup with the Match function to make your summary dynamic according to a change in the input. To begin the session, click here!

Dynamic Hlookup with Match Function

Choose and Offset formulas are solid choices if we have a situation about the conditional selection of some cells. Let's dive into this session to get a better understanding!

Choose & Offset

Our datasets might have some missing values or errors at times, in order to avoid these errors reflecting in the formulas we construct, lets understand how the IFERROR function works with lookup functions.

Nesting Index Match inside IFERROR

Advanced Function Workbook

Charts & Graphs

Always, visuals have a greater impact than mere numbers. Therefore, it is recommended that one be able to represent their data in a visual form. But before we jump onto getting meaningful results, lets explore the various options we have at our disposal.

Basic Charts

In continuation with our previous session, this lesson will help you better your data representation skills and provide insightful visual representations of the dataset. So lets start right away! 

Advanced Charts

You cannot undo what has already been done. Oh, wait, this statement is not applicable for Excel.  You can use the dynamic charts to reflect changes in the raw data. Let's look at how that can be done.

Dynamic Charts using Functions & Data Validation

Charts & Graphs Workbook

Financial Data Analysis

If a manager wants to forecast how their output will appear in the future due to changes in the business environment, they may utilize this function and know how! Managers may also want to know how the revenue will vary if the cost or selling price changes. All of this can be done using a few simple features in Excel. Learn about them now!

Sensitivity Analysis (Data Table) & Scenario Manager

When we specify which input to change, Excel’s Goal Seek tool produces the desired output in a matter of seconds. By accepting certain limits on those inputs, Solver takes it up a level. Let’s see how we can do that in this session.

Goal Seek & Solver

It's crucial to filter and arrange your data when you have a big dataset with lots of different values. One key feature that facilitates this is the pivot table. It is really simple and organizes complicated datasets to produce relevant insights. Begin learning right away.

Pivot Tables

Financial Data Analysis Workbook

Time Value of Money

Everyone wants to know whether their current investments will pay off in the future. You can better analyse your investments by learning the basic concepts of time value of money in this session. Start this session right away!

It's critical to determine whether a project you wish to work on will generate positive results. You can get a fair idea of it with the aid of these Excel functions. Begin now.

Advance Functions

Do you have a large debt that you wish to pay off? Then creating a debt amortization schedule is essential. This final session will assist you in accomplishing that. Start now!

EMI Calculation

Time Value of Money Workbook

Macros

By using a macro, you can eliminate performing boring and repetitive tasks through automation. Learn how to record, run and test a macro to save your valuable time.

Introduction

It is critical to know how to record and run a macro in multiple ways! Get started with this session to master it! 

Record & Run a Macro

Knowing how the codes run behind a macro is essential to make tweaks to it later on. Lets learn how to test a macro right away!

Test the Macro

Macros Workbook

Basics of VBA

We look for automation whenever we perform any tasks. VBA is the programming language that helps us do exactly that! If macros are actions, VBA is the code that is generated for those actions.

Introduction to VBA

Understanding the layout of the VBA interface is necessary to get started with coding. Lets jump onto this session to familiarize ourselves with the various windows inside of the VBA interface

The VBA Interface

It's crucial to get a hang of how codes are written in VBA, along with how they get stored inside the window. Let's dive into this session or get a better understanding!

Getting Started with Coding

The VBA MsgBox function is used to display messages to us, in the form of a pop-up style message box. Start with this session right away to know more! 

The MsgBox function

The purpose of VBA is to automate our experience by avoiding manual form of inputting values. Delve into this session to learn how to create useful variables using Dim function.

Creating Variables 

Logical functions are the best way to understand which values meet a set criteria. Watch this video to get started with constructing some logic statements.

Building Logic using If Else

Manually typing the same value can get time consuming and lead to errors. Learn how to create codes using the Case method to avoid it!

Case Method

When we run codes, we want to make it as dynamic and quick as possible. Delve into this session to get hands on the most efficient way of building logic statements

Do While Loop

VBA Workbook

Bonus Session

In this bonus session, you will learn how to analyse the strength of a relationship between two variables and then forecast the future relationship between them based on that evaluation

Regression Analysis

Shortcuts

Basic to Advanced Excel</span

Sanjana is a research analyst with experience in collaborating with private equity clients to support their investments, conduct portfolio analysis, and create investment memorandums.

Welcome to the course on Excel for Finance on Upsurge.club that covers a range of topics that are essential for mastering this tool. Some of the key topics that are covered include Excel Layout and Settings, basic and advanced excel functions, charts and graphs, financial data analysis, time value of money, and macros which will provide you with a solid foundation for becoming a successful stock trader. In lesson 1, Introduction, you will learn about the significance of acquiring expertise in Excel and get access to the course outline. In lesson 2, Excel Layout &amp; Settings, you will delve into various tabs and keyboard shortcuts in Excel, as well as investigate essential workbook settings that are beneficial for configuring our financial setup. In lesson 3, Basic Financial Setup, you will gain the knowledge to establish an Excel layout tailored for financial modeling. In lesson 4, Basic Functions, you will cover diverse functions, including IF, SUMPRODUCT, CONCATENATE, and many others. In lesson 5, Advanced Functions, you will delve into advanced functions such as VLOOKUP and HLOOKUP, INDEX and MATCH, CHOOSE, OFFSET, and other advanced techniques as part of our learning journey. In lesson 6, Charts &amp; Graphs, our focus will be on helping you master the creation of both basic and advanced charts. Additionally, you will explore the intricacies of crafting dynamic charts through the use of functions and data validation. In lesson 7, Financial Data Analysis, you will examine various methods for analyzing financial data, including sensitivity analysis and scenario analysis. Additionally, you will gain proficiency in utilizing functions such as Goal Seeker and Solver, along with learning the application of Pivot tables. In lesson 8, Time Value of Money, you will acquire knowledge of both fundamental and advanced functions related to the time value of money. Additionally, you will be proficient in calculating EMI. In lesson 9, Macros, you will gain the skills to automate tasks through the use of macros. In lesson 10, Basics of VBA, you will delve into essential concepts of VBA, covering topics such as coding, variable notation, utilizing functions like MsgBox, constructing logic using IF-Else and Case methods, and much more. In lesson 11, , you will explore the process of conducting regression analysis in Excel, understanding how to analyze and interpret regression models for informed decision-making. Course Highlights: - Learn the basic financial set-up - Explore dynamic functions &amp; formulas - Analyse data using Regression and What if Analysis - Learn Macros and VBA Who is this course for: - Beginners who are eager to learn basic to advanced Excel - Anyone who is considering making their career in the field of finance

Covered all the basics of excel that are used in the industry.

Basic to Advanced Excel

STOCK_MARKET

Become proficient in Microsoft Excel, a must-have skill, & learn practically with practice sheets and assignments.

Learn advanced Microsoft Excel in 5-Hours - Learn basic functions & formulas, data analysis, pivot tables, vlook-up and more. 4.3+ Rated | 1800+ Enrolled.