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
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
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
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
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
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.
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