Mastering Business Processes, Competitive Strategy, and Advanced Excel Data Analysis

Posted on Nov 11, 2025 in Computers

Module 1: Business Process and Competitive Advantage (20%-30%)

Understanding Business Processes (BP)

1. Definition of a Business Process

   A sequence of activities that a company performs to achieve a specific goal. These activities can be automated or manual, and they are organized in a specific order to ensure efficiency.
   Example: Order fulfillment process.

2. Business Process Management (BPM)

   BPM is a technique for optimizing processes that businesses employ to perform tasks, serve customers, and generate revenue.

   • BPM involves managing multiple input streams and the inter-communication of business processes to generate desired business outcomes within the least time possible.
   • It takes an input, puts it through the Business Process, and creates an output.

3. The Three Pillars of BPM

   • People
   • Processes
   • Technology

4. The Business Process Management Lifecycle

   The BPM lifecycle is made up of 5 repeatable steps:

   1. Design: Identify current processes, evaluate workflows, and plan improvements.
   2. Modeling: Map and test workflows under different scenarios to understand how processes work.
   3. Execution: Assign tasks, deploy processes, and monitor initial performance.
   4. Monitoring: Track workflow performance in real-time to detect inefficiencies.
   5. Optimization: Continuously improve processes to achieve better outcomes.

5. Benefits of BPM

   • Increased Visibility: Business operations become more evident, helping in identifying bottlenecks.
   • Reduction of Lead Time: Reduces the time required for company operations, converting leads into customers faster.
   • Clearly Defined Employee Roles: Good BPM ensures a better definition of the duties and roles of employees by cementing strong business regulations.
   • Adds Cross-Functional Dimension: BPM reduces multiple service calls to a small number using a functional automation approach.
   • Unified Systems and Operations: When BPM is used, no business process stays in a silo.

Competitive Advantage

6. Definition of Competitive Advantage

   The ability of a company to outperform its competitors in the marketplace.

7. Three Types of Competitive Advantage

   • Cost Advantage: A company that can produce goods or services at a lower cost than its competitors.
   • Differentiation Advantage: Companies that offer products or services unique in some way, perceived as being of higher value than competitors’ offerings.
   • Network Advantage: A company that can leverage its connections with other companies, customers, and stakeholders to gain an advantage over others.

8. Benefits of Competitive Advantage

   • Increased profitability
   • Sustainable growth
   • Higher market share
   • Better bargaining power

9. Strategies for Competitive Advantage

   • Innovation
   • Cost Leadership
   • Differentiation
   • Customer Service

10. Porter’s Five Forces of Competitive Advantage

    These forces describe the competitive environment within an industry:

    1. Existing Competition: The intensity of the battle within the industry.
    2. New Competitors: The threat of market disruption from new entrants.
    3. Suppliers: Their power to control costs and input quality.
    4. Customers/Buyers: Their power to demand more or less value.
    5. Substitutes: The risk of customers switching to alternative products or services.

Module 2: Data Analytics: Advanced Excel 1 (20%-30%)

Excel Chart Fundamentals

1. Chart Definition

   A graphic that visually represents numeric data.

2. Chart Components

   • Data points
   • Plot area
   • Chart area
   • Chart title
   • Legend
   • Y-axis
   • X-axis

3. Recommended Charts Feature

   Access via Insert > Recommended Charts.

4. Resizing and Moving Charts

   • Resize: Use handles on the sides or edges.
   • Move: Click and drag when the directional cursor is visible.
   • Move Chart to New Sheet: Use the Chart Design tab, click Move Chart. Select “New Sheet” for a dedicated sheet or “Object in” to move the chart to an existing worksheet.

5. Quick Layouts, Styles, and Colors

   • Quick Layouts: Select the chart, go to the Chart Design tab, Chart Layouts group, and click Quick Layout.
   • Quick Styles/Colors: Select the chart, go to the Chart Design tab, Chart Styles group. Choose a style or click Change Colors to select a color scheme.

Hierarchy Charts

6. Hierarchy Charts (Treemap and Sunburst)

   These charts visualize data organized into categories (branches) and related subcategories. Two main types are Treemap and Sunburst.

   How to Insert: Select data, go to the Insert tab, click the Hierarchy Chart button, and select Treemap or Sunburst.

   • Treemap: Use when you want to compare the relative size of each branch and then each subcategory inside.
   • Sunburst Chart: Shows more hierarchy because it visualizes data as rings, with top-level categories forming the inner ring.

Statistical Charts

7. Statistical Charts (Histogram and Pareto)

   • Histogram

     Similar to a bar or column chart, except each column represents a range of values. Used to visualize frequency. Each column is called a bin. The class interval is the value range between bins.

     How to Create: Select data, go to Insert > Insert Statistic Chart > Histogram.

   • Pareto Chart

     The columns are sorted from largest to smallest, with a line chart on a secondary axis showing the cumulative total percentage.

     How to Create: Select data, go to the Insert tab, click

     (the statistical chart icon), navigate to the Histogram section, and select Pareto.

8. Box-and-Whisker Chart

   Represents the range of values for each category based on dividing the values into quartiles.

   Quartiles: The values that divide the list of values into four equal parts.

   • Quartile 2 (Median): The middle value that divides the list of values in two.
   • Quartile 1 (Lower Quartile): The value that divides the first half in half again.
   • Quartile 3 (Upper Quartile): The value that divides the second (upper) half in half.

   In the box-and-whisker chart, the box represents the range between the upper and lower quartiles (the interquartile range), with the ‘X’ marking the mean (average) value. Lines called whiskers extend above and/or below the box to show values outside the quartiles. Outlier values (values outside the box by more than 1.5 times the length of the box) are represented by a single dot.

   How to Create: Select data, go to the Insert tab, click

   (the statistical chart icon), and select Box-and-Whisker.

Waterfall Charts and Sparklines

9. Waterfall Charts

   Shows a running total as values are added or subtracted.

   How to Create: Select data, go to the Insert tab, select the Waterfall icon, then Waterfall. If your chart includes a final data point that represents a total, click any data point to select the data series, then double-click the data point you want to set as a total. In the Format Data Point task pane, click the Set as total check box.

10. Sparklines

    Represent a series of data values as an individual graphic within a single cell. They update immediately with data changes.

    How to Add:

    1. Select the data range. Ensure there are empty cells to the right of the data where you want the Sparklines to appear.
    2. The Quick Analysis Tool button appears near the lower right corner of the selected range. Click it.
    3. Click the Sparklines tab.
    4. Click the button for the Sparkline type you want (Line, Column, or Win/Loss).

    How to Remove: Select the cells containing the Sparklines, then on the Sparkline tab, in the Group group, click the Clear button.

11. Formatting Sparklines

    • Changing Sparkline Type

      Click one of the cells containing Sparklines to activate the Sparkline tab. In the Type group, click the Line, Column, or Win/Loss button.

    • Adding Markers

      Markers are visual representations of data points. Click anywhere in the Sparklines, then check the options you want in the Sparkline tab, Show group:

      • To add markers for every data point, click the Markers check box.
      • To add a marker for just the highest value, click the High Point check box.

    • Changing Quick Style

      Click a cell containing Sparklines to activate the Sparkline tab. In the Style group, click the More button to expand the gallery, and click a style to apply it.

    • Changing Color (Individual Sparkline)

      To format a single Sparkline separately from the group:

      1. Click the individual Sparkline.
      2. On the Sparkline tab, in the Group group, click the Ungroup button.
      3. To change the Sparkline color, click the Sparkline Color button arrow and select the color.
      4. To change the marker color, click the Marker Color button. Point to the marker type (e.g., Negative Points) and select the color.

Module 3: Data Analytics: Advanced Excel 2 (20%-30%)

Data Entry and Ranking Functions

1. Autofill and Flash Fill

   • Autofill: Click and drag the Fill Handle in the desired direction.
   • Flash Fill: Insert a column where you want the data to appear. Start typing, and if Flash Fill detects a pattern, you can press Enter to use it.

2. RANK.EQ and RANK.AVG Functions

   • RANK.EQ

     Returns the rank of a number in a list. If more than one value has the same rank, the top rank of that set of values is returned. If you were to sort the list, the rank of the number would be its position. Requires: Number, Ref. Optional: Order.

   • RANK.AVG

     Returns the rank of a number in a list. If more than one value has the same rank, the average rank is returned. Requires: Number, Ref. Optional: Order.

What-If Analysis Tools

3. Analyzing Data with Goal Seek

   Excel’s Goal Seek function lets you enter a desired value (outcome) for a formula and specify an input cell that can be modified to reach that goal. Goal Seek changes the value of the input cell incrementally until the target outcome is reached.

   Steps:

   1. Go to the Data tab, Forecast group, and click the What-if Analysis button, then Goal Seek.
   2. Enter the outcome cell in the Set cell box.
   3. Enter the outcome value you want in the To Value box.
   4. Enter the input cell (the cell containing the value to be changed) in the By Changing cell box. This cell must be referenced in the formula in the outcome cell (directly or indirectly) and must contain a value.
   5. Click OK. Click OK again to accept the solution or Cancel to return the input cell to its original value.

4. Activating and Using the Solver Add-in

   Solver is a data analysis tool that evaluates multiple variables affecting a formula and produces optimal values for those variables within defined constraints to result in a value you specify.

   Activation:

   1. Go to File > Options > Add-ins.
   2. Near the bottom, in the Manage box, select Excel Add-ins, then click Go…
   3. Check Solver Add-in and click OK.

   How to Use Solver:

   1. On the Data tab, in the Analysis group, click the Solver button.
   2. In the Solver Parameters dialog, enter the objective cell reference in the Set Objective box (the cell containing the formula you want Solver to solve for).
   3. Click the radio button to find the Max (maximum) or Min (minimum) result, or click Value Of and enter a specific target value.
   4. In the By Changing Variable Cells box, enter the cells you want Solver to manipulate. These cells must be precedents to the formula in the objective cell.
   5. Add constraints (limitations on acceptable values) by clicking the Add button next to Subject to the Constraints box.
   6. When all constraints are added, click the Solve button.

Lookup and Reference Functions

5. VLOOKUP Function

   The VLOOKUP function finds a value or cell reference in a column range and returns another value from the same row.

   Syntax: =VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup)

   Steps:

   1. Select the cell. Go to the Formulas tab, click the Lookup & Reference button, and select VLOOKUP.
   2. Enter the Lookup_value (the cell reference you want to find a corresponding value for). This value must be located in the first column of the cell range in the Table_array argument.
   3. Enter the Table_array (the range of cells or range name that contains the lookup data).
   4. Enter the Col_index_num (the column number within the Table_array from which the function should return a matching value).
   5. (Optional) Enter Range_lookup. Type FALSE if you want to find only an exact match.
   6. Click OK.

6. MATCH Function

   MATCH returns the position of a specific value in a single row or column array. It requires two arguments and one optional argument.

   • Lookup_value: The text, number, or logical value you want to match.
   • Lookup_array: The range of cells grouped in a single row or column that contains the value you want to look up. (Do not include the header row or label column.)
   • Match_type (Optional):
     • 0: Exact match only.
     • 1 (or omit): Finds the first position where the value is less than or equal to the lookup value (requires array sorted smallest to largest).
     • -1: Finds the first position where the value is greater than or equal to the lookup value (requires array sorted largest to smallest).

   How to Use: Select the cell. Go to the Formulas tab, Lookup & Reference button, select MATCH. Enter Lookup_value, Lookup_array, and Match_type (use 0 for an exact match).

7. INDEX Function

   INDEX returns the value at the intersection of a specified row and column in an array.

   How to Use:

   1. Select the cell where you want the formula.
   2. Go to the Formulas tab, Lookup & Reference button, select INDEX.
   3. In the Array box, enter the range of cells or name for the entire data array.
   4. In the Row_num box, enter the reference to the cell that contains the row position you want to look up.
   5. In the Column_num box, enter the number of the column that contains the data you want displayed in the formula results.
   6. Click OK.