0

Course / Course Details

EXCEL for Business and Finance

  • Dr Joyan Dsouza image

    By - Dr Joyan Dsouza

  • 106 students
  • 30 Hours
  • (5)

Course Requirements

Access to Computer with MS EXCEL

Course Description

Welcome to the Certificate Course on Excel for Business and Finance offered by M.Com (Finance and Analytics), St Aloysius, Mangaluru. In this course, we will take you through a journey from basics to best practices in analytics using Microsoft Excel.

Excel for Business and Finance enables you to utilize Microsoft Excel for the business environment. You will learn how to use Microsoft Excel efficiently using functions, shortcuts, decision making tools, What-If analysis, statistical tools, multi-page systems and look ups, data menu and ribbon, financial tools, and solver add-in, in over 8 Modules consisting of 31 Lessons. This course will cover lessons of the book “Next Generation Excel: Modeling in Excel for Analysts and MBA’s” by Isaac Gottlieb.

We have refined the course content to suit learners from diverse backgrounds to help you enhance your practical and analytical skills. We hope you find this course both useful and engaging. The course will be self-paced. You can watch videos anytime. However, it is necessary to complete the Module Quizzes and Final Assessment before the course deadline. For further clarification, contact +91 89038 38343.

The Course Content is as follows:

Module 1: How to Use Excel Efficiently

Lesson 1: AutoFill

Lesson 2: Selection

Lesson 3: Formulas, Functions, Relative and Absolute Addressing

Lesson 4: Naming Cells and Ranges

Lesson 5: Excel Charts

Module 2: If Functions and Text Manipulations

Lesson 6: IF Functions

Lesson 7: Text Manipulation

Module 3: Statistical Tools

Lesson 8: Descriptive Statistics

Lesson 9: Frequency Distribution

Lesson 10: Statistical Regression

Lesson 11: Data Analysis – Statistics Add-in

Module 4: What-if Analysis

Lesson 12: Naming Cells for Decision Making

Lesson 13: What-if Analysis and Goal Seek

Lesson 14: Sensitivity Analysis – One-Way and Two-Way Data tables

Lesson 15: Scroll Bars

Module 5: Multi-Page Systems and Lookups

Lesson 16: Multiple Budgets

Lesson 17: Lookup Tables

Module 6: Data Menu and Ribbon

Lesson 18: Sorting Data

Lesson 19: AutoFiller

Lesson 20: Data forms and features eliminated in Excel 2007

Lesson 21: Group and Outline Data

Lesson 22: Subtotals

Lesson 23: Pivot Tables

Lesson 24: Data Mining using Pivot Tables

Module 7: Excel Financial Tools

Lesson 25: NPV and IRR

Lesson 26: XNPV and XIRR

Lesson 27: Frequently used Financial Functions

Lesson 28: Amortization Tables

Lesson 29: Accounting Depreciation Functions

Module 8: Using the Solver Add-In

Lesson 30: Beyond Goal Seek

Lesson 31: The Solver Add-In Optimizer

Course Outcomes

  • You will develop an understanding of Microsoft Excel
  • You will learn how to prepare professional Excel sheets and to use Excel functions
  • You will learn to use financial tools available in Excel for financial modelling

Course Curriculum

  • 3 chapters
  • 76 lectures
  • 4 quizzes
  • 30 Hours total length
Toggle all chapters
1 Introduction to AutoFill and Customs List
6 Min

Please Use the Excel Sheet named "AutoFill" for practicing during the video.


2 Illustration For AutoFill
7 Min

Please Use the Excel Sheet named "AutoFill" for practicing during the video.


3 Create Customs List
6 Min

Please Use the Excel Sheet named "AutoFill" for practicing during the video.


4 AutoFill Options and Right Drag AutoFill
9 Min

Please Use the Excel Sheet named "AutoFill" for practicing during the video.


5 Selecting an Entire Sheet
6 Min

Please use your solved Excel sheet of Lesson 1 for practicing this lesson. Efficient Way of Selecting the entire sheet: Click on the intersection point of Column A & Row 1. (The intersection point is called "Select All" button)


6 Selecting a Section
2 Min

Please use your solved Excel sheet of Lesson 1 for practicing this lesson.


7 Selection Shortcuts
10 Min

Please use your solved Excel sheet of Lesson 1 for practicing this lesson.


8 Extend and Add Mode
11 Min

Please use your solved Excel sheet of Lesson 1 for practicing this lesson.


9 Formulas, Relative and Absolute Addressing
18 Min

Please download the worksheet provided in the attachment for practice while watching the video.


10 Other Functions
9 Min

Please use the attachment provided for the lesson to work while watching videos


11 Naming a Single Cell
7 Min

Please use the Excel attachment provided in Lesson 3 for practice throughout this Lesson.


12 Naming a Range of Cells
8 Min

Please use the Excel attachment provided in Lesson 3 for practice throughout this Lesson.


13 Naming a Range of Cells - Example
8 Min

Please use the Excel attachment provided in Lesson 3 for practice throughout this Lesson.


14 Name Menu and Name Manager
13 Min

Please use the Excel attachment provided in Lesson 3 for practice throughout this Lesson.


15 Creation of Charts
9 Min

Please download the Excel Attachment provided in this video for practicing throughout the lesson.


16 Formatting of Charts
4 Min

Please use the Excel Attachment provided in "Creation of Charts" Video for practice. Please Note: For Excel 2007 and 2010 users To format chart, click or select the chart, then go to chart tool ribbon, select layout ribbon and modify chart title, gridlines, and legends as required.


17 Working with 3D Charts
6 Min

Please use the Excel Attachment provided in "Creation of Charts" Video for practice.


18 Simple IF Functions
15 Min

Please download the worksheet provided in the attachment for practice while watching the video.


19 Simple IF Function Example
13 Min


20 Nested IF Functions
8 Min


21 Nested IF Function Example
9 Min


22 Text to Column Wizard
7 Min

Please download both the attachments for practice during Lesson 7.


23 Opening Text Files
6 Min

Please  download the attachments provided in the first video. The text file attached in the previous video will be used during this video.


24 Example
6 Min

Please use the Excel attachment provided in Video "Text to Column Wizard" for practice during this video.


25 Calculating Descriptive Statistics
15 Min

Please download the Excel attachment provided with this video below to practice while watching.


26 Frequency Distribution Tables
9 Min

Please use the Excel sheet provided in Lesson 8 for practice.


27 Frequency Distribution Charts
6 Min

Please use the Excel Sheet provided in Lesson 8 for practice


28 Regression Using Scatter Chart
5 Min

Please use the Excel sheet provided in this lesson for practice


29 Linear Regression using Excel Functions
8 Min

Please use the Excel attachment provided in Lesson 10 for practice throughout this video.


30 Descriptive Statistics
9 Min

Please download the Excel attachment provided in this video for practicing throughout the lesson.


31 Frequency Distribution using Histogram
4 Min

Please use the Excel attachment provided in Lesson for practice


32 Naming Cells - for Decision Making and Modeling
13 Min

Please download the Excel attachment provided with this video below to practice while watching.


33 Goal Seek
18 Min


34 Goal Seek - Example
18 Min


35 One way Data Table
11 Min


36 Two way Data Table
4 Min


37 What-if Analysis example
7 Min


38 Scroll Bars
17 Min


39 Scroll Bars (Continuation)
3 Min


40 Multidimensional Excel Workbook
7 Min


41 Multi-Page Budgets Summary sheet
4 Min


42 Exact Match Lookup
5 Min


43 Approximate Match Lookup
5 Min


1 Sorting Data
11 Min


2 Custom List for Sorting Data
4 Min


3 Auto Filter
11 Min


4 Data Form Activation
4 Min


5 Working with Data Forms
4 Min


6 Grouping and Outlining Data
7 Min


7 Clearing Outline
2 Min


8 Applying Subtotal to Single Data
7 Min


9 Applying Subtotal to Multiple Categories
7 Min


10 Copying Grouped Data
7 Min


11 Pivot Tables
7 Min


12 3D Column Chart in Pivot Table
7 Min


13 Grouping Data
7 Min


14 NPV
10 Min


15 IRR
5 Min


16 XNPV
7 Min


17 XIRR
4 Min


18 PMT
5 Min


19 PV & FV
5 Min


20 NPER
4 Min


21 RATE
3 Min


22 Monthly Payment Loan - PMT
4 Min

Please download the attachment provided for practice during this lesson  Question : Consider a home loan/mortgage of Rs.600,000 to be paid off in monthly installments over a periods of 25 years, applying an interest rate of 6.00 percent. You have to create a loan amortization table for the 300 monthly payments (25 years X 12 months/years).In addition,you ,may want to calculate the cumulative interest in certain years.


23 Amortization date - Month and Payment Date
5 Min


24 PPMT - Principal
5 Min


25 Amortization Table – Balance, CUMIPMT, CUMPRINC
5 Min


26 Depreciation
11 Min


27 Using Solver to overcome the limitations of Goal Seek
20 Min


28 Solver Add-In – Optimizer (Linear Example)
18 Min


29 Solver Add-In – Optimizer (Non-Linear Example)
13 Min


1 Assignment 1 [Quiz]
1 Hour


2 Assignment 2 [Quiz]
1 Hour


3 Test 1 [Quiz]
1 Hour


4 Test 2 [Quiz]
1 Hour


1. A Auto fill
2. B_Formulas, Functions, and Relative and Absolute Addressing
3. C_Creation of Charts
4. D_IF Functions
5. E_Opening Text Files
6. E_Text Manipulation
7. F_Calculating Descriptive Statistics
8. G_Statistical Regression
9. H_Descriptive Statistics
10. I_Naming-Cells
11. J_Multi Page Budgets
12. K_Lookup
13. L_Sorting Data
14. M_Auto Filter
15. N_Data Forms
16. O_Group and Outline Data
17. P_Subtotals
18. Q_Pivot Tables
19. R_Data Mining using Pivot Tables
20. S_NPV-IRR
21. T_XNPV
22. U_XIRR
23. V_Frequently used Financial Functions
24. W_Amortisation Tables
25. X_Depreciation
26. Y_Solver Add-In Optimizer
27. Z_Assessments

Instructor

Dr Joyan Dsouza

Dr Joyan Dsouza is an Assistant Professor and Programme Coordinator of M.Com (Finance and Analytics) in the School of Commerce, Finance and Accountancy at St Aloysius (Deemed to be University), Mangaluru. He has completed his M.Com in Business and Finance from Pondicherry Central University, Pondicherry, has qualified UGC NET, and completed his Doctoral Studies in "Performance Analysis of Mutual Funds" from Davangere University. He has to his credit the “Research Analyst Certification” and “Mutual Fund Distributor Certification” from the National Institute of Securities Market, Diploma in Investment Management, and Licentiate Certification from the Insurance Institute of India.

He has two years of industry experience as a Subject Matter Expert in Finance in SPi Global Technologies and six years of teaching experience at St Aloysius (Deemed to be University), Mangaluru. He is also a partner at San-Jean Insurance Advisors and AJ Equities, a mutual fund distribution firm.

His active research areas include equity markets, insurance, ethical consumption, e-waste management, supply chain finance and mutual funds. He has presented six research papers in national and international conferences and published two research papers in peer reviewed and four research papers in UGC Care listed journals.

His key skills include financial modeling using MS EXCEL, Insurance Advise, Financial Planning, Tax Planning, and Data Analysis using SPSS, R Studio, and PSPP and has been invited as a resource person for workshops on data analysis using software. He is also an active member of the Indian Commerce Association.

5 Rating
1 Reviews
111 Students
2 Courses

Course Full Rating

5

Course Rating
(1)
(0)
(0)
(0)
(0)

Sign In or Sign Up as student to post a review

Student Feedback

Course you might like

Beginner
STOCK MARKET OPERATIONS
0 (27 Rating)
This Stock market course provide essential knowledge for anyone looking to invest or pursue a career in finance. Introdu...
Beginner
Tourism Operations
4 (1 Rating)
Module -1: Introduction to Tourism & Different types and forms of tourism (15 hours)Tourism- Meaning and Definition; Sig...

You must be enrolled to ask a question

Students also bought

More Courses by Author

Discover Additional Learning Opportunities