Power BI Challenge 7 - Purchases, Inventory And Sales!

Challenges Purchases, Inventory And Sales
1

Challenge 7- Purchases,Inventory And Sales!

So here we are for another instalment of the Enterprise DNA challenge.

It’s been an awesome few weeks and we’ve have had some amazing submissions across the board. We are covering some serious ground in terms of scenarios and industries not to mention the sharing of ideas, tips, tricks and approaches.

So here it is all the 7th instalment of the EDNA challenge.

Remember its open to everyone so don’t be afraid to get stuck in.

To learn more about the challenge and how it works be sure to check out the forum below!

If you’re not sure of anything just reach out to me.

Remember: The weekly winner will receive a complimentary membership to the platform that they can share with anyone and the opportunity for your work to be showcased across our channels. There is also a newcomer category with some great prizes on offer so be sure to get involved.

THE BRIEF

So this weeks challenge comes at the request of a user who has emailed in an idea.

After reviewing with the team we feel there is a great opportunity here for us to learn from what is a typical scenario faced by many businesses in various industries

Things are going to be a little different this week. The ask is very specific you must stick to it as best you can.

There are 3 datasets coming from their own respective sources.

Purchases-This data shows us the date that our business raised a purchase order for a particular product and the amount of the product.

Receiving- This when we as a company took stock of the materials, we have ordered e.g. the products arrived in our warehouse.

Billing-This is the transactional data of what we sold to which customer.

The ask is to produce a single page summary that gives us the below insight into our business.

There are 3 key things we want to see:

  • From us placing a purchase order what is the time elapsed before we receive the goods into our warehouse
  • From us receiving goods what is the time elapsed before we are billing for the goods
  • Analyse the Billing

Note: Remember that the data is coming from three different sources so there is no existing keys or relationships between the tables

SUBMISSION DUE DATE - Sunday, 27th September 2020 (PST)

Please can you all submit your PBIX files to powerbichallenge@enterprisedna.co

The data set can be downloaded below.

Data Challenge 7.xlsx (141.5 KB)

Best of luck!

Any issues or questions please reach out.

Haroon

Enterprise DNA

10 Likes
2

Here we go again!

Read the brief carefully any issues don’t be afraid to reach out.

Thanks all.

Haroon

3

Hi @haroonali1000

Here column “Tons” is the total cost or it is the quantity?

1 Like
4

Awesome scenario this time. Here we go!!!

1 Like
5

Interesting!
Quick first view questions:

  1. Is it normal that the Purchase Quantity Tons is slightly different then receiving tons?
  2. Is the billing Unit cost the final price proposed to the client? (it is different then the unit cost from the purchase data)

Ex:
Purchase Order = 68000499
Material = 60136

1 Like
6

Quantity

7

Hi @MK3010 @alexbadiu,

Tons is the quantity.

Unit cost is cost per tonne that we have acquired it for.

Unit Price is the retail price that we sell to clients.

It is possible that purchase tons can be different received.

Thanks
Haroon

8

@alexbadiu It’s a normal scenario where we receive quantity less than ordered. Normally, it happens with large orders where the quantities are delivered over the period of time. So, one PO can have many DN’s i.e. Deliveries and Invoices.

9

Thank you for your reply @haroonali1000.
@MudassirAli, I agree with the logic, but in the example provided, the quantity received exceeds the quantity ordered.

1 Like
10

@alexbadiu it’s true the other way round 1 to 2 percent of the time only in rare occasions. So before closing the Purchase Order it is necessary to adjust the amount in the PO. The data doesn’t say how many POs are closed otherwise would have been interesting to analyze that. If there are lots of POs with more quantity received then it’s a problem.

11

Hi All,

Just to note that there are some entries in Receipts and Billings without associated purchases in this instance you can assign a ‘No Associated Purchase Order’

Thanks
H

1 Like
12

Exciting, thanks Haroon

Just remember everyone, we are looking for only 1 page! this time around

It will require some creative thinking.

Looking forward to diving in.

Sam

2 Likes
13

Hi Dear
Hope you doing well
I wanted to ask for something:

Do we have an issue here in Power Bi Challenge 7

Purchase orders / Tons = 48.634
Received / Tons = 58.286
Difference – 9.652 Ton (More received than purchased)
For product nr.
60051 = 151.46 ton
60230 = 15.30 ton
60796 = 20.32 ton
61980 = 43.19 ton
61991 = 1.09 ton

              231.37 ton (received material that we have not purchased)

For Around 240 billed product we know unit price but not unit cost because they doesn’t exist as product in received table

14

@fatjanpaloja As @haroonali1000 mentioned :

I hope it helps.

Thanks.

15

It can be that material received is more than ordered . This could happens with some bulk materials

16

What I see here is that the same material has different dates of Order, receiving and sales , and how to calculate the difference between all those dates

17

But there also seem to be receipts with a receipt PO but no matching purchasing PO or details. How can that be? Or have I misunderstood?

18

That’s odd. I haven’t looked at the dataset though. The receipts can’t have PO # if there is no Purchase Order for that receipt.

19

I looked at it and most of the receipts (95%) with no matching PO’s are in Jan 2020. This might be because the PO’s were generated prior to Jan 2020 and we are receiving the material in year 2020.

1 Like
20

Yes, that could be it, but it looked odd. Thanks for checking. Appreciate it.

1 Like