SABA 1 Program, NITTE (Deemed to be University), Karnataka

NITTE (Deemed to be University) 6th Floor, University Enclave, Medical Sciences Complex, Deralakatte, Mangaluru – 575018, Karnataka
Module 1 - Accurate, Efficient and Smart Formula construction

Module 1: Accurate, Efficient and Smart Formula construction

Start Date: Tuesday, February 25, 2020 | End Date: Monday, March 02, 2020

Youtube Playlist: Click here to watch videos

Assignment Submission Link: Click here to submit

Module 2 - Format, simplify and aggregate data using logical functions

Module 2: Format, simplify and aggregate data using logical functions

Start Date: Tuesday, March 03, 2020 | End Date: Monday, March 09, 2020

Youtube Playlist: Click here to watch videos

Assignment Submission Link: Click here to submit

Module 3 - Finding a needle in a haystack - Doing Automated table lookups

Module 3: Finding a needle in a haystack – Doing Automated table lookups

Start Date: Tuesday, March 10, 2020 | End Date: Monday, March 16, 2020

Youtube Playlist: Click here to watch videos

Assignment Submission Link: Click here to submit

Module 4 - Show don’t tell – Present Statistical Information Effectively

Module 4: Show don’t tell – Present Statistical Information Effectively

Start Date: Tuesday, March 17, 2020 | End Date: Monday, March 23, 2020

Youtube Playlist: Click here to watch videos

Assignment Submission Link: Click here to submit

Module 5 - Time Value of Money

Module 5: Time value of Money

Start Date: Tuesday, March 24, 2020 | End Date: Monday, March 30, 2020

Youtube Playlist: Click here to watch videos

Assignment Submission Link: Click here to submit

Module 6 - Discounted Cash Flow Applications

Module 6: Discounted Cash Flow Applications

Start Date: Tuesday, March 31, 2020 | End Date: Monday, April 06, 2020

Youtube Playlist: Click here to watch videos

Assignment Submission File: Click here to submit

Module 7 - Depreciation & Amortization

Module 7: Depreciation & Amortization

Start Date: Tuesday, April 07, 2020 | End Date: Monday, April 13, 2020

Youtube Playlist: Click here to watch videos

Assignment Submission Link: Click here to submit

Module 8 - Making Data Meaningful & Central Tendency & Dispersion

Module 8: Making Data Meaningful & Central Tendency & Dispersion

Start Date: Tuesday, April 14, 2020 | End Date: Monday, April 20, 2020

Youtube Playlist: Click here to watch videos

Assignment Submission Link: Click here to submit

CERTIFICATE DISTRIBUTION
  • DATE

    TO BE ANNOUNCED

SYLLABUS

Course Instructor

Professor Ratan Gupta, B Tech, IIT KGP, CFA & FRM. He has 13 years of experience in Industry, taught at VCU Graduate programs and successfully coached CFA Level 1, 2 & 3 candidates as well as FRM Level 1 & 2 candidates.

 

Grading Scale

>=91: A Grade; 81-90: B Grade; 71-80: C Grade; 61-70: D Grade; < =60 Unsatisfactory.

Instructor reserves the right to curve the grade

 

Assessment Plan

8 weekly assignment (excluding diagnostic test) with 11% weight each

Course Description: Most often students struggle in MBA program due to lack of clarity in foundation level Business mathematics and statistics concepts as well as spreadsheet skills. This certification program is particularly designed to overcome this difficulty by taking students through a series of business math and stat concepts as well as by carefully selected applications of such selected concepts. This program was originally designed to prepare Masters Students at the Virginia Commonwealth University (VCU) to be successful in MS in Finance, MS in Decision Analytics, MS in Global Marketing Management and MS in Information Systems programs.

 

Expected Learning outcome: At the end of the course, besides familiarity with Microsoft Excel, students will also refresh foundation level concepts in business Mathematics & Business Statistics.

 

Online Pedagogy: Students learn how to solve problems and underlying concepts from video lessons prescribed for the weekly assignment. Thereafter, students will solve Assignment problems in Excel and submit their answers for grading. Students can clarify doubts either by posting comments to the instructor or by chatting with instructor during pre-specified office hours. After the due date, students will receive graded assignments & learn from feedback comments and marks

 

Online Office hours by Course instructor (for doubt clarification): Two days a week 90 minutes each (days and timing will be notified at the beginning of each week)

 

Work Load: Students are expected to spend 2-3 hours per week preferably during evening during online-office hours to learn from video lessons and solve assignment problems. Students should follow the steps below while working on a typical assignment:

  • Step 1: Click the assignment link sent to you by the instructor
  • Step 2: login using your user name (your email ID) and password
  • Step 3: Reset your password (Recommended at the beginning of first assignment)
  • Step 4: Login again (If you have logged out)
  • Step 5: Download video lessons and assignment question files from the link provided
  • Step 6: Watch Videos lessons
  • Step 7: Solve assignment questions
  • Step 8: Clarify doubts with instructor/Subject Matter Expert during office hours on Zoom platform
  • Step 9: Login again (if you have logged out)
  • Step 10: Upload completed assignment file on or before the due date
  • Step 11: View your marks and download graded assignment file and review expert comments to understand where and how you lost marks

 

Certification:  Successful students will be awarded Certificate with grade

 

How to Enroll? Students will be registered into the program using the Name and official email id provided by the Institution. Once registered, students can login using unique username and password to download and upload assignment files.

 

DURATION & SYLLABUS: Nine weeks online program wherein students will be administered nine weekly assignments as below:

 

Technology Requirement: Students must have their own laptop with Microsoft Office 2007 or later or should use the computer lab provided by the Institution. Also, students must have access bandwidth provided either by the institution or personal bandwidth in order to download and upload assignment files and video lessons. Students will be sent Zoom invite for online office hour meetings.

Please download and install zoom in your desktop/laptop/mobile devices before joining a zoom meeting. Here is the link for zoom – https://zoom.us/download#client_4meeting

 

Zoom at Google Play Store: https://play.google.com/store/apps/details?id=us.zoom.videomeetings&hl=en

Zoom at App Store: https://apps.apple.com/us/app/zoom-cloud-meetings/id546505307

 

 

*Note: Assignment start and due dates & office our timings may change due to factors that affect College semester time table.

DETAILED SYLLABUS

Unit 1:

Accurate, Efficient and Smart Formula construction: Formulas are the lifeblood of spreadsheets. Writing accurate formula is preferred over faster formula creation as it is time consuming to clean up errors when bad formulas are created and copied to other cells. This unit focuses on creation of accurate as well as efficient formulas.

Problem sets include – computing Tax Dollars & After Tax income for employees; Computing Monthly salary payment;  Multiplication Table Challenge; Find maturity amount of fixed deposit; Sales and Gross Profit Projection;  Computing Monthly sales over a period; Computing Commission to the sales  person; Computing GST; Computing average mileage of cars; Computing Transportation cost of each package in a courier company;Computing restaurant wise general insurance premium for a restaurant chain; Computing screen wise general electricity cost for a multiplex chain

 

Unit 2:

Format, simplify and aggregate data using logical functions : An analyst frequently applies condition to data to extract information meeting the criteria. Constructing nested functions using logical conditions is a skill which should be in every analyst’s toolkit. In this unit we will understand how to use Excel’s built in functions (IF, AND, OR, AverageIF, AverageIFS, SumIF, SumIFs, CountIF, CountIFS) to achieve desired objectives.

 

Problem sets include  – Creating ageing schedule for trade receivables; Creating rating band in performance appraisal; Computing sum of salary of employees meeting some criteria; Computing amount spent in advertisement by advertising medium; Apply logical formula to highlight the company’s on the basis of ratios;  Compute number of transaction in a supermarket which are meeting a criteria; Compute total revenue based on transaction date and product; Compute the profit of the companies in an industry sector; Finding out the salesperson who have achieved their target; Finding out employees who have successfully passed training program

 

Unit 3:

Finding a needle in a haystack – Doing Automated table lookups: Searching data efficiently in a large data set is one of the key skills an analyst should possess. Excel provides a variety of tools and functions to achieve this. This module covers  Multi-level vertical and horizontal sorting; Custom Filtering with numbers, date, text, color and wild cards; Single horizontal and vertical lookups;Two way lookup; Employing Match and Index to cover up the deficiencies in vertical and horizontal lookup; Enhancing lookups with wild card; Trapping and eliminating errors while working with lookup functions

 

Problem sets include – Finding the last salary drawn for an employee using Employee Code; Finding the ticker symbol for a company name; Finding ISIN code when the company ticker symbol is given; Finding the rating band based on the points in the appraisal; Computing car sales when the brand and month is given

 

Unit 4:

Time Value of Money: Familiarity with time value of money concepts, formulas, and spreadsheet solution techniques are a must for analyst in every field. Everything else in financial math is built on this foundation and it is important that the concepts are absolutely clear.

 

Problem sets include: Compute and analyze interest rate as sum of real risk free rate and premiums to compensate for the risk; Compute Effective Annual Rate given stated rate and frequency of compounding (finite period); Compute Nominal rate given Effective annual rate and frequency of compounding; Compute FV & PV of a single sum of money, an ordinary annuity, an annuity due; Compute PV of a constant and growing perpetuity; Solve time value of money problems. Finding one unknown when other four time value items are known; Compute CAGR using various excel functions

 

Unit 5 :

Discounted Cash Flow Applications:  An analyst need to understand that shifting of one cash payment at one time to its equivalent at another time. Computing Net present value allows converting an irregular set of cash flows back to the present and allows comparing one project with another. This unit helps students to understand the use of timeline in modeling

 

Problem sets include – Solve shiproblems using timeline; Compute PV for a series of unequal cash flows;  Compute and analyze NPV and IRR of an investment; Given NPV and IRR for mutually exclusive projects, find the project to be accepted.; Compute and analyze payback period, discounted payback period, and profitability index (PI) of a single capital project

 

Unit 6 :

Depreciation & Amortization:  It is important that everyone regardless of their specialization and career need to understand & compute the tax benefits arising out of depreciation and amortization. This unit helps to understand them in a practical way to differentiate between cash expenses and non-cash expenses and differentiate between Amortization and Depreciation

 

Problem sets include – compute periodic depreciation, accumulated depreciation, beginning book value, ending book value for various depreciation methods:  straight line, Sum of year digits, Declining balance, Double declining balance, Modified Accelerated Cost Recovery System (MACRS) – Used in USA, Unit of production method, Component Method; Understand how the choice of depreciation method and assumptions concerning useful life and residual value affect depreciation expense, taxes and profits; calculate amortization expense using different amortization methods for intangible assets with finite lives

 

Unit 7:

Making Data Meaningful – Aggregating and Summarizing Qualitative & Quantitative data: There are many business questions that require the collection and analysis of qualitative data. Cross-tabulation provides a methodology for observing the interaction of several variables in a set of data. A data analyst should be able to quickly summarize large data sets. Cross tabulation in excel can be achieved conveniently using Pivot tables and other inbuilt excel functions.

 

Problem sets include – Creating frequency distribution from a survey data; From a sales transaction data –  Compute sales by salesmen; Compute sales by product; Compute sales grouped by month; quarter; year; Compute sales by territory; Compute dynamic sales data using filters;  Create dynamic worksheet using pivot table

 

Unit 8:

Central Tendency & Dispersion: Measures of central tendency and dispersion provide a convenient way to describe and compare sets of data. Basic summary statistics form the foundation in making decisions based on the data.

 

Problem sets include – Calculate the average daily wage and the variance of the distribution of wages of all the employees; Compare the CEO’s salary with the median / average salary of all the employees; Compute the percentile rank of Salesman quarterly sales;  Find the rank of a company whose market capitalization is given; Find the list of companies who are in the top quartile based on annual profit; Compare the average salary of undergraduates and compare with the median salary of undergraduates; Compute multiple modes in a given salary dataset; Produce Summary Statistics of quarterly profit of companies in the Software sector; Compare the skewness and kurtosis of daily stock return with the normal distribution.

 

Unit 9:

Show don’t tell – Present Statistical Information Effectively. A picture is worth a thousand words: Visualization is an integral part of statistical business processes, an effective data analyst uses a combination of text, tables and graphics to maximize data’s strength in conveying various types of information.

 

Problem sets include – Show Source of website traffic; Sow Employee distribution by department; Show Employee distribution by location; Show Employee Compensation distribution by department; Show Employee distribution by salary bracket; Show Total Employee count over a time period; Show Employee demography (age, gender) over period/department wise; Show inflation rate over years; Show unemployment rate over the years for multiple countries;Show monthly sales by stores; Show percentage sales across stores over a period;

Show annual sales of a high growth company using log scale; how sales of company using; dynamic chart; Show Sales trend using sparkline; Show top product sales; Show sales by region; Show sales by product category; Show risk vs return for a financial product; Show data using Gartner’s Magic Quadrant