Sales & Operations Planning / Integrated Business Planning

Balance supply and demand to increase profitability and customer service, using Excel

Created byLuke Rochester

Last updated 9/2021

English

What you'll learn

Create great looking Excel Dashboards from scratch and transform them into iPhone and iPad Apps without writing a single line of code.

Course content

6 sections • 36 lectures • 3h 7m total length

DIY Dashboards for Excel and iPhones: An overview of the whole course9:37
In this video we race through the entire course curriculum. Don't worry, we'll take it a lot slower during the actual training videos and we will explain each step as we go.

Your First Excel Dashboard: Data1:47
We start right at the beginning with a very simple table of data in a single Excel spreadsheet. In this video I talk a little bit about the sample data itself. In other words I answer the question: What is it an example of?
Check out the resource section to download a copy of the sample file that you see in this video.
Your First Pivot Table3:46
Perhaps one of the most important videos in the whole course. In this video you learn how to create your first Pivot Table.
Check out the resource section to download a copy of the sample file that you see in this video.
Your First Pivot Chart1:11
After you have created a Pivot Table the Pivot Chart is easy. In this video you see just how easy.
Check out the resource section to download a copy of the sample file that you see in this video.
Your First Excel Dashboard: Slicers1:43
The slicers are perhaps my favourite feature of Excel. In this video we see how to add them. In a later video we will look at how to use them.
Check out the resource section to download a copy of the sample file that you see in this video.
Your First Excel Dashboard: Formatting2:33
OK so formatting is not my favourite thing to do ... but it's important for the end users. So here is a video showing you how to make a spreadsheet look like a dashboard.
Check out the resource section to download a copy of the sample file that you see in this video.
Your First Excel Dashboard in Action2:35
And finally here is our finished dashboard. If you followed the steps you should be able to do the same things with your dashboard. You have now created your first Excel Dashboard - CONGRATULATIONS !
Check out the resource section to download a copy of the sample file that you see in this video.
Your First Excel Dashboard: Quiz

The One Page Report: Creating multiple pivot tables2:42
With the right technique you can create several Pivot Tables in a matter of minutes. In this video I show you how. In the resource section you will find a sample of the file shown in the video.
The One Page Report: Calculating Dimensions and Elements3:55
With multiple Pivot Tables feeding the dashboard we need to create a summary table of calculations to pull the right data from the right pivot table into the dashboard. In this video we look at how to bring dimensions and elements into the summary calculation table. In the resource section you will find a sample of the file shown in the video.
The One Page Report: The Time Dimension1:46
Most dashboards incorporate a time dimension and in this video I show you how to bring it into the summary calculation table. In the resource section you will find a sample of the file shown in the video.
The One Page Report: Summary Calculations and the GetPivotData function7:04
One of the most important calculations in the whole dashboard is the GetPivotData function. In this video I show you step-by-step how to use it to summarise the results into your summary calculation table. In the resource section you will find a sample of the file shown in the video.
The One Page Report: Sparklines1:24
Sparklines are a great way to provide a lot of information in a glimpse. In this video we look at how easy it is to add sparklines to your dashboard. In the resource section you will find a sample of the file shown in the video.
The One Page Report: Calculated KPIs (Key Performance Indicators)7:06
In this dashboard you will be able to calculate KPIs (Key Performance Indicators) on the fly. In this video I will show you how. In the resource section you will find a sample of the file shown in the video.
The One Page Report: Chart and Slicers2:53
In this video we add a chart to the dashboard and connect it to all of the slicers. In the resource section you will find a sample of the file shown in the video.
The One Page Report: Formatting2:23
In this video I show you techniques to make the final solution look less like an Excel spreadsheet and more like an Information Dashboard. In the resource section you will find a sample of the file shown in the video.
The One Page Report: Testing and error capture4:19
As with all-things-software you will most probably have a few gremlins and bugs to kill off before releasing your masterpiece into the wild. In this video we look at some common problems and how to fix them. In the resource section you will find a sample of the file shown in the video.
The One Page Report in Action2:21
The dashboard that we lovingly refer to as "The One Page Report" is shown in all its glory in this video. How did your dashboard turn out? Do they look similar? In the resource section you will find a sample of the file shown in the video.

Unlocked dashboard file attached0:19
In the resources section of this lesson you will find an unlocked version of the sales and operations planning excel dashboard.
Overview of the supply and demand dashboard2:04
In this lecture you see an overview of the dashboard in action.
Raw data for supply and demand / S&OP dashoard7:42
In this lecture we go through the raw data that the dashboard is based on.
Start building the dashboard - the main pivot table.4:41
Now that you're familiar with the raw data you can build the calculation table framework that will drive the main parts of the dashboard.
Create multidimensional calculations (AKA GetPivotData calcs)6:40
It's time to grab that strong coffee and launch into the calculations that make up the summary calculation table.
The S&OP Chart12:15
From the end-user's perspective this is probably the most valuable part of the dashboard. It's a chart that shows supply, demand, inventory and customer service ... all on the one chart !!
Dashboard slicers and formatting10:16
In this lecture we add the all important slicers. We also do all of the formatting that makes this dashboard look less like an excel spreadsheet and more line a sophisticated business intelligence tool.

One Business App: Overview & demo of this section of the training course.3:46
In this video I will show you how to use the sample file to turn your spreadsheet into an iPhone App. The sample file shown in the video can be found in the resource section.
One Business App: Setting up a Key Performance Indicator Table3:29
In this video we start to set up the Key Performance Indicator Table.
One Business App: Adding Dimensions and Elements to the KPI Table2:33
You can use One Business App to show any &/or every part of your organisation. In this video we learn how to add Dimensions and Elements to the KPI Table.
One Business App: Creating KPI Aggregations3:19
Having created your KPI Table I show you how to create Aggregations points. As sample file is included in the resource section.
One Business App: Creating a KPI Hierarchy3:47
Having learnt about aggregation points in the last video we move on to create a whole hierarchy where, for example, users can navigate from the top (total company) down through departments into mid-levels of the company finally resting at the most granular level of the individual business entities.
One Business App: Navigation5:15
Using unique identification numbers for each KPI and "Parent" IDs for most KPIs we can create a fairly sophisticated navigation structure. In this video you see this in action. There is a sample file available in the resource section.
One Business App: Multi-level Navigation5:04
The overall concept of this video is similar to the last video however we enable users to drill into more detail and we control the multi-level hierarchy using this method. See the sample file in the resource section.
One Business App: Importing final KPIs into the app.8:03
Here is where the rubber hits the road ... time to import your KPIs into One Business App. The sample file is in the resource section.
One Business App: Conclusion0:49
In this lecture we wrap up this section of the training course.

The Business Intelligence Roadmap20:02
As many as 80% of BI Projects fail, typically from companies focusing too much on the technology and not enough on the people and the business. In this lecture I will show you how to avoid the 80% failure rate.
Using FileMaker as a Business Intelligence Tool20:13
Why FileMaker is such a powerful Business Intelligence tool.
Working with Tableau and FileMaker8:14
A video demonstrating how you can use Tableau with Claris FileMaker

Requirements

This course is designed specifically for non-programmers and as such students do not have to have any programming experience whatsoever. In fact you do not even have to have the intention to ever want to program. This course is not about "code cutting". It is helpful however to have at least a basic understanding of spreadsheets and we will be using MS Excel 2010 in the demonstrations, so it is a benefit to have Excel 2010 (or a later version) already loaded before you start.

Description

Build an S&OP/IBP dashboards using only Excel

Build Dashboards & Business Apps without writing a single line of code, all you need is Excel.

In a world first we have designed a training course that helps the average Excel user create an Excel dashboard and an iPhone app without having to write a single line of code.

When we build the dashboards we only use tools that come standard with Excel – like Pivot Tables, Pivot Charts, Icons and Sparklines (and don't worry there isn't any visual basic or even a single macro in our solution).

And here's the best bit … Claris FileMaker (an Apple subsidiary) has released a free database for iOS called "FileMaker Go". It works natively (i.e. online or off-line) on iPhones and iPads.

The program you download from the resource section will automatically convert a simple spreadsheet into a sophisticated Business App that works with FileMaker Go. Therefore you will be able to distribute your own apps to as many people and iOS devices as you like.

More advanced users can even host an online version which will work with most browsers including Android. The advanced version can integrate with everything from SQL to SAP and Claris has just released Claris Connect, which can connect your app to API's via a simple workflow. These advanced (multi-user) techniques are beyond the scope of this training course however advanced users, BI specialists, data analysts, data scientists, programmers and app developers may still want to take this course to refine core skills in multidimensional modelling and analysis.

However, this training course is squarely aimed at the typical Excel user who wants a simple method for building their own Dashboards and Business Apps.

Who this course is for:

This course is designed for Excel users; Managers, executives, all types of business people, office workers, scientists, engineers, doctors, teachers, students ... basically anyone who "thinks for a living" (i.e. Knowledge Workers) and knows how to use Excel.
Advanced users and developers may like to take this course to give them a foundational grounding on Dashboards and Business Intelligence before they embark on the technical aspects of deploying a larger scale Information Dashboard.

Sales & Operations Planning / Integrated Business Planning

What you'll learn

Explore related topics

Course content

A FREE preview of the entire course curriculum, kept short for busy people.1 lecture • 10min

Building your first Excel Dashboard6 lectures • 14min

The One Page Report - A more sophisticated Excel Dashboard10 lectures • 36min

Supply and demand dashboard, great for Sales & Operations Planning7 lectures • 44min

One Business App9 lectures • 36min

Deep Dive3 lectures • 48min

Requirements

Description

Who this course is for: