New user? Register as member

Automate Sales Incentive Calculation with Excel Power Query and Power Pivot

Sales incentive calculation can be highly complicated if it involves different targets for salespeople, distributors, outlets, countries, month, quarters and years.

Due to the complexity of the sales incentive scheme, using Excel worksheet functions and multiple worksheets to perform the calculation is very manual and tedious. It could take days to complete the sales incentive calculation and it is also prone to human errors.

But all these problems can be eliminated if we use the New Excel Power Query function and PowerPivot Table for the calculation. Power Query makes use of a single source of data for all the incentive calculations so that you don’t have to maintain multiple copies of the data. No more multiple worksheets. At the same time, Power Query captures every step of the calculations so that the calculations can be reuse for the next round in exactly the same way. All you have to do is to add in the latest source of data and the results can be generated in less than a minute instead of days.

Your own laptop installed with the latest version of Excel. The latest version now is either Office 365 Pro Plus or Office 2021 PRO. You can find out the office version on your computer by following the instruction in this video https://www.youtube.com/watch?v=A--0B6N3wqA

Objective

Upon completion of the course, participants will be able to:

• Calculate the individual/distributor incentives for the month
• Calculate the group incentive for the month
• Calculate the group incentives for the quarter
• Combine and present all the above incentives in one Pivot Table

Outline

Who should attend

Profile of Jason Khoo

Jason Khoo is a Microsoft Certified Trainer (MCT), ACTA certified trainer of 20 years. He is a Microsoft Certified Power BI Data Analyst Associate and a data analytics practitioner of 30 years. During his time of employment, he has worked in Tibs (currently SMRT), MobileOne Ltd, 3M and Virgin Mobile.

During his tenure, he has worked with multiple databases, extracted and downloaded information from systems and run numerous analysis. These analyses spanned across many departments such as Finance, Sales, Marketing, Human Resources, Payroll, etc.

After his employment, he worked as a data analytics consultant for many companies, including Discovery Asia, 3M, Tanah Merah Country Club, Johnson and Johnson Vision Care, National Environmental Agency, Timberland, etc.
His data analytics skills combined with Excel allowed him to deliver many reports to the clients without them having to incur software cost. These reports include Dashboards, Top 10 charts, Risk Analysis, Business Models, KPIs, etc.

As a trainer, he is passionate about transferring his knowledge and imparting his skills to his participants. To day, he has trained thousands in his face to face workshops and had conducted talks for thousands as well.

Trans-Island Bus Services Ltd (Tibs) –Operations and Revenue Analytics, Passengers Travelling Behaviour Analytics
• Daily tracking of revenue,
• Prepare KPI for LTA,
• Fare revision analysis

Mobile One Asia Ltd (M1) – Financial analytics, Customer Call Usage Analytics
• Full company budgeting and business planning,
• Monthly Analysis,
• KPI reporting

3M – Sales, Cost and Inventory analytics
• Daily and Monthly Sales Forecasting,
• Analysis of Marketing Budget,
• Analysis of Inventory and shipments.
• Cost Analysis for new products (BOM analysis)

Virgin Mobile – Retail, Marketing, Payroll, Admin, IT, Finance Analytics
• Daily KPI reporting,
• Monthly Analysis for Finance, Payroll,
• Developed Fully Automated Business Model driven by KPI