Excel 2007 Dashboard for Supply Chain Activities.

[M. Qayyum Anwar]

I have been assigned a task to restructure the Contract Administration and Procurement Department due to its poor performance in the past.

I'm planning to have Deshboard having KPIs and Flags/Major Milestones to monitor all activities from:
Award of contract from Client -> Purchase Request -> PO -> Supplier -> Manufacturing -> Shipping -> Custom Clearance -> Delivery and Handing over. An idea is to have a Dashboard like Excel worksheet to have full picture of all POs.

May I ask our group members for a help.

Thanks in advance for your time.

 

[Simayan]

Hi you can make a pivot & use slicer & time line for the same

 

[M. Qayyum Anwar]

Hi Simayan,

Thank you for your reply. Do you have some example of this.

 

[Faseeh]

Asalam o alikum Qayyum,

Can you elaborate further how your data is organized so that we can proceed. i am interested in this dashboard assignment.

 

[Simayan]

U can the following option. The Jpeg is what I have made for my Financial Master dashboard

 

[M. Qayyum Anwar]

Thanks for all. I am developing one excel sheet containing all necessary information and will share with all you soon.

Regards,
Qayyum

 

[M. Qayyum Anwar]

OK. I have upload my first working files. I have two cases as follows:

Files: SPX-Status.......xlsx is product specific details.
Files: PO-MF-M15.xlsx is Master PO File.

Now I want to have a dashboard showing following status:

1. PO Status (Open, In Progress, Hold and Closed based on Year)
2. Supplier Details (Invoice Status and Variation etc....)
3. Shipping Status (Planned and Actual ETD (Estimated Time of Departure) and ETA(Estimated Time of Arrival),)
4. Freight Forwarder and Clearing Agent Status (This part is still incomplete and will update soon. I appreciate if somebody has an experience on this part so that we can share).

Note: I have put some conditional formatting specially for Variation, ETD and ETA etc...

Thanks, Qayyum

 

[M. Qayyum Anwar]

Hi guys,

Anybody is there to discuss my problem....

 

[Faseeh]

Yes, your data on SPX-Status is not in the shape of a list, can we convert it to a list?

 

[M. Qayyum Anwar]

Yes, we can convert it in the shape of list.

 

[M. Qayyum Anwar]

Salam Faseeh,

I was bit busy during this time but still i was able to modify my file and added some data from my current record.

Please ignore all the files earlier. We can work on the POMF-M15.xlsx

I need a dashboard:

1. PO Status (Open, Closed, Active, In Progress etc...)
2. PO Summary on yearly basis.
3. Currency Based record (Total business in year # in EUR or USD....i.e. in 2015 USD business $ xx,xx,xxx.xx)
4. POs record per Region or Per project

enough for now.

Best regards,

 

[Faseeh]

Walakum Salam Qayyum,

Since Ramazan has started in my country and i am getting little spare time, i try to revert by tomorrow with some sample file.

 

[Faseeh]

Dear Qayyam,

sorry for late reply, please see attachment.

 

[M. Qayyum Anwar]

Thanks Faseeh. I will go through and will get back to you.

 

[Faseeh]

Dear Qayyum,

Any update on this?

 

[M. Qayyum Anwar]

Sorry Faseeh due to Ramadan I am not able to do what i suppose to. Anyway I am working on this and will get back to you soon.

Thanks for your concern. Regards, Qayyum