Builds 20 - Sales & Production Analysis

Challenges Sales And Production Analysis
1

@FPastor

Hello All,

WELCOME TO CHALLENGE 20.

“AIKEEN INDUSTRIES - SALES & PRODUCTION ANALYSIS”

I hope that you are keeping well.

Challenge 19 “EDNA Call Centres Data Reporting” shocked us all and produced 55 great entries with more than 200+ report analysis pages, 150+ navigation buttons, bookmarks, and tooltips. The variety of native and customs visuals in the reports is a proficiency in Data Visualization.

During Enterprise DNA formidable May’s Analytic Summit . We were able to squeeze a one-week challenge “A Blast from the Past – The Remake of Challenge 7”. The challenge revamping a previous report from no other than @Mudassir Syed Ali.

We continue reviewing the format of our challenges initiative and have some excellent material in the pipeline. Please make sure you keep up with us across social media and here in the challenges platform.

We introduced a dedicated space for Enterprise DNA Challenge participants on LinkedIn, bringing together Enterprise DNA platform members and non-members with a simple goal, how to build game-changing solutions with Power BI by learning, practising, sharing, and participating in EDNA Challenges. The group counts now with 800+ members and keeps growing daily, please join us using link below.

So, we are back for another instalment of the Enterprise DNA Challenge. Challenge 19 was another great display of the talent and creativity of our members.

Sincere congratulations to Abu Bakar N. Alvi our overall challenge winner. A great well done to all participants, as truly there was something in every report to learn from.

Administration

Just want to take a moment to cover of some administration around the challenge as we really want to see more of you getting involved.

To learn more about the challenge please see the link below:

There is no need for me to mention the benefits of participating and the impact which can have on your development. We as Enterprise DNA genuinely believe that getting hands on and practicing with real scenarios is the fastest way to proficiency .

So, I again encourage you all to try and get involved with the challenge, any issues please let us know, we are more than happy to help.

Check out the previous challenge entries here.

While the learning and experience of participating in the challenge is extremely rewarding itself, we also have some great prizes on offer for category winners. Please see below the categories.

Overall, Winner - all entrants are eligible

First Time Participant winner - open to any Enterprise DNA member who is taking part in the challenge for the first time.

Winning Non-member - open to entrants not currently eDNA members.

Out of the Box Thinker – for those digging deep into their analysis.

Creative Head – for those bringing a bit of extravagance into their report and analysis.

Prizes

There are some excellent prizes on offer from free membership (for all category winners) and more – much more!!

Free Subscriptions to Enterprise DNA Learning Platform

Other prizes.

And much more…

The chance to have your work showcased across the Enterprise DNA network.
The chance to win complimentary membership.
Receive personalized feedback around your development.
Learn from peers and experts.
Practice what the workplace is looking for enhancing your credentials as a Power BI expert.
Get experience across different industries.
Understand common pitfalls in solutions.
Get involved in discussions around enhancing your solutions and learn about new features.

11 Likes
2

The Brief

Congratulations to our Power BI Team , your excellent work in Challenge 19 has been, once more, rewarded with a new contract.

Aikeen Industries, a leading manufacturer and seller of gourmet crips & nachos products and has requested our services.

Aikeen Industries is looking at its operations wanting to sync all aspects of its business from production to forecasting. Aikeen Industries’ priority is to ensure a strong production, selling and forecasting business model to take the company to the phase 1 of their expansion plan.

In preparation for this, Aikeen Industries is seeking to implement and adopt Power BI as its analytical tool for the future. Aikeen Industries has requested a deep analysis of its current business model.

The dataset sample is provided by, Aikeen Industries, which is a real extraction from its databases comprising information about Production, Forecast, Product, Machines, Finished Good Inventory and Sales Orders.

About the dataset

One Excel Workbook

Tab 1 – Actual Production

Tab 2 – Production & Sales Forecast

Tab 3 – Sales Orders

Tab 4 – Finished Good Inventory

Tab 5 – Products

Tab 6 - Machines

Data-dictionary

Brief in PDF format

Important Read!!

From Aikeen Industries CEO – Aikeen G.

Thank you for engaging in this project with us. We have provided you with a real sample data exported from our databases.

The following questions must be answered in your report:

  • Sales team performance by comparing Sales Forecast to Production Forecast.
  • Inefficiencies in forecast by comparing Actual Production to Production Forecast.
  • Identify products with high inaccuracies in forecasts based on historical data (2020 till date)
  • How many shortages of orders (compare Sales Orders vs Finished Goods Inventory?
  • Calculate Sell-through rate: Sell-through rate = (Units Consumed/Units Produced)
  • Accuracy of Forecast = [(Actual Production/Production Forecast) *100]
  • Identify items in Finished Goods Inventory for which we have no or low sales (consumed means sold).
  • Fill rate = [(**Total Units in inventory-Consumed Units)/Total Units in inventory] *100
  • Predict the remaining 2022 forecast using Actual Production vs Production Forecast
  • Predict the 2022 sales team performance using Sales Forecast vs Production Forecast

**Total Units in inventory = Opening plus produced minus consumed

Important Read -

Our project start date is 03/06/2022 and your final reports must be submitted by the 02/07/2022.

Report Developer Role

It is your job as an analyst to prepare a deep-dive analysis report about Aikeen Industries business and operational model.

Reports are not limited by the number of pages or techniques. Feel free to use all the techniques at your disposal – tooltips, drill throughs, page navigations, etc.

That is all for the brief!

Submission of entries

To be considered within the competition, entries are due no later than 11:59pm GMT Saturday, July 2, 2022.

If you are not already following Enterprise DNA on LinkedIn please do so and join our challenge group at

How to submit:

Email the completed PBIX file to

powerbichallenge@enterprisedna.co

federico.pastor@enterprisedna.co

Take an image of your report and post it to the Enterprise Challenges Group

If you are a platform member, take an image and the “Publish to Web URL” of your report, then post them to the Enterprise DNA forum. If you are not a member of Enterprise DNA learning platform do the same in the Enterprise DNA Challenges Group.

Take the image plus URL and post them on LinkedIn tagging Enterprise DNA to say, “I accepted the Enterprise DNA challenge.”

We always encourage all participants to do a brief description of how they approach the project and share their experience of participating in the challenge and sharing it on the forum/social media.

If you need any help with publishing, please reach out to one of the team manager for assistance (post in the forum/LinkedIn group or email to federico.pastor@enterprisedna.co

Conclusion

A great opportunity to learn a valuable skill of being able to produce and calculate Production and Forecast Reports within Power BI.

The techniques covered here are massively reusable across the board.

Get involved!

Any issues or questions, please reach out.

Federico Pastor - Enterprise DNA

Dataset

C20 - Dataset.zip (1020.5 KB)

9 Likes
3

@FPastor - quick question on the dataset. The tab “Sales Orders” has a “Quantity” column and then 14 columns each representing a day in May. Can you clarify whether these columns are related (the sum of the May columns do not add up to the quantity column). I am assuming that the units in each of the May days represents the quantity ordered - but am unsure how the Quantity column fits in here.

1 Like
4

Hi Tim,

I have passed your query to the provider of the dataset, in the interim. Can you please unpivot the date columns and see if the quantity fit better :sunglasses:

5

I unpivot - no fit.
It looks like the Quantity in Sales Order is Finish good Inventory - Closing quantity

1 Like
6

Regarding Finish Good inventory
It seems Closing (for Product) = Opening + Produced +Consumed
but it works except

  • for Product 1 (as you can see on the picture):

image
image962×168 8.55 KB

  • and for Product 220, Product 268

Good luck everyone.

1 Like
7

@mspanic

You’re 100 % right.

The quantity column in the Sales Order table has an exact match with the figures of the Closing column in the Finished Goods Inventory table. There’re certain Products in the Sales Order table which doesn’t have the closing figure but it’s there in the Finished Goods Inventory table.

Thank you Maja :grinning:

1 Like
8

Hi @FPastor , any expected update during this challenge? Or can we assume this is the “final” dataset"?

2 Likes
9

@Josimar_Biosse

This is all, we expect some minor clarifications but dataset in complete.

Federico
PS. Or maybe not :sunglasses: (kidding) it is.

2 Likes
10

@FPastor

I’am glad that we clarify about Quanity column in Sales order.

I have another question - I just want to check it out - if I miss something from C20 dataset?

Regarding demands :

in C20 dataset I see only 14 Sales dates (in Sales Order) in May 2022
and regarding Production Actuals - data from 2020

There is no historical data from 2019 till date.

Thanks

1 Like
11

@mspanic

Hi Maja, it should read 2020 till date, sorry for the typo.

Regards,

Federico Pastor

1 Like
12

Gotta say, I absolutely love how @mspanic is already diving DEEP into this data, and it only got released earlier today…

Rock on, @mspanic!

3 Likes
13

If you’re already here in this thread, it’s unlikely you need additional motivation to get fired up about this Challenge, but here’s a post I did earlier on LinkedIn that may be of interest:

Could not agree more strongly with Federico Pastor. Wiithout exaggeration, I think this challenge can be a career-maker. This is actual corporate data, entirely of the type you can expect to see as a Power BI consultant or in-house developer.

If you put in the necessary effort and crush this Challenge, you will have an entry in your portfolio that you can confidently show to any prospective employer as hard evidence of your ability to clean, model, analyze and visualize real-world data.

The winning entries will posted permanently in the Enterprise DNA Showcase and seen by tens of thousands of Power BI users.

And last but not least, the overall winner will have the opportunity to present an Enterprise DNA webinar, walking users through how they created their report.

Think that all might be something that could boost your career…?

2 Likes
14

@FPastor Thank you.

@BrianJ - regarding my June schedule I have only couple of days (mostly nights) if I want to finish this (or at least try). Start playing with data as soon as I saw the post.

Hardly wait to see the finding from data and others results.

Great challenge and organization.

Enjoy analyzing

2 Likes
15

It would seem Quantity in Sales Order worksheet is a redundant column and can be deleted then?
Not sure what purpose it serves?

16

Hi @Federico,
I am really liking this dataset but I am a bit confused and not sure if I am missing something:

  • actual production ceases at 5 May and the actuals sales are from 7 to 20 May only (units consumed). How do you calculate sell through (Units consumed/Units produced) when the periods don’t match and only have sales for 13 days?
  • Finished goods are as at 24 May, yet the sales orders are 7 - 20 May. Are we to assume these are unfilled sales orders?
  • To calculate sales performance, it states to compare sales forecast to production forecast. Anyone can forecast a high number, Sales performance should be judged on actuals (sales Orders)?
2 Likes
17

@KimC

Hi Kim,

I am taking your question to the dataset owner and will be back to you in not time.

Regards,

Federico

1 Like
18

Hi @KimC,
Yesterday I was asking myself these same questions after loading the data into Power Query.
In the Production&Sales Forecast table, if we group by Date and Product, not all of them have 2 records, one for each type of forecast.

Regards

2 Likes
19

Hi @jafernandezpuga ,

Me too.
In Production&Sales Forecast table I pivot column to have separate columns Production and Sales Forecast side by side.

And then I assume - that some of products may produce in advanced (like Product 10 in November 2020)
image

There are other types of gaps in forecast (when there are Production Forecast without Sales forecast - like for Product 10 example in February 2021) - but analyzing of Inefficiencies in forecast (by comparing Actual Production to Production Forecast) is one of demands so it will go in that part with recommendations.

Best regards

3 Likes
20

@mspanic & @jafernandezpuga

Thank you for your clarifications and views into the question posted by @KimC

It is great to see our platform members interacting and helping each other.

Big, once more, THANK YOU!
Federico Pastor

2 Likes