December 1, 2020
I have a data table (1) that requires pulling data from another table(2) to autofill some cells, I'm trying to use XLOOKUP to do this with an IF statement.
The data table will have multiple like F1 entries. I have to track two different sub-projects that make the final product, which each are independent of each other for timeline/amount, but influence the final outcome.
End result will be some form of Dashboard that will show the progress of products through phases in the pipeline timeline to a predicted market introduction time/amount. This is done by comparing the predicted to the estimated/actual completions of each phase as data is collected during and end of each phase.
I'm still not sure if I have the correct data structure, I'm starting out doing this with manual entries, then hope to expand it to link to each department and have a final table that will auto-populate and the dashboard will be live. That's my hopes, it all begins with data structure and collection.
Thanks for any help
PS: I'm in the agriculture seed industry and the pipeline process starts in the research department and spans all departments until market introduction of a product.
I've attached a couple of jpg that might help.
October 5, 2010
December 1, 2020
Thank you for your help, sorry for not getting back to you sooner, my company is going through a complete global ERP change over, it has been keeping me quite busy.
I would like to give some background on my situation, it may or may not be unique. I stated in my previous post that I work for an agricultural seed company that through research and production of seed when then sell to farmers. I have been with my company for 21+ years working in several different roles from research to manufacturing and the one thing we haven't had was a true product pipeline process. most departments work in silos and information and data was restricted. January 1st of 2020 there was a change in upper management's decision to get a product pipeline process in place, which I was given the task. Until we have all of the data accessible and structured in a way that it is useable, I will have to manually keep the pipeline timeline updated, but they would like to have a dashboard showing the products, their progress through the timeline and weather or not they are going to be on time.
Since we work mostly with hybrid (requiring both male and female) plant, the process has to keep track of mainly two aspects of the three components, the hybrid and the two parents. Will the final product meet the desired market introduction date and volume.
It's not a difficult process, just hard to describe and it has many variables that change outcomes throughout the process. The process has 5 phases, starting from research through introduction sales. Each product is independent from each other, not like making the same bolt over and over again, but the process of advancing them is basically the same but the outside influences are very fluid (environment, disease, pest, human error), which can occur from planting to seed inventory.
To accomplish these task, a lot of data is collected that aids in day to day decisions, but for my process I only need relatively few data points from each phase but I have to compare it back to a model which estimates what is required for parents and hybrid are to meeting the expected timeline and volume
With all that said, here is what I am doing at this point (right or wrong):
New product is released from research, I input it into a model with year of sales introduction and volume, the model ("Crop Plan") will then backwards calculate what each phase will need to produce of the parents and when (some of this is a manual input), final phase is the production of the hybrid.
Once the product starts down the process road, I track two data flows (estimated and actual), the "estimated", which is what the department believes it the timeline and volume will be at handoff to next phase, this is compared to the "Crop Plan" numbers, if timeline is extended or volume is lower, then the health indicator is changed. This "estimated" data can be changed during the process, but most of the time, only once or twice.
The other flow is the "actual", this is the actual volume and date of the product, this is at the harvest end of each process and this will override the estimate data and will adjust the timeline and volume.
As we start a process, the "Risk Level %" of meeting the "Crop Plan" is very high and our "Confidence Level %" is very low, as we fill in "actual" data points, "Risk Level %" will start to lower and "Confidence Level %" will start to increase.
The dashboard will show basically very few data points, Hybrid, Sales Introduction Year, Sales Introduction Volume, Health status.
I have been racking my brain on how to make the data structure that will allow me to make the "Crop Plan" and then collect the data for the "Estimated" and "Actual", then compare the "Crop Plan" to them. This complete process can take upto 2-5 years.
I have attached my thoughts, but stuck on how to proceed and have many questions, am I structured correctly? do I use Power Query?
I have been watching a lot of Youtube video's on Excel and PowerBI, Gantt charts? most of them deal with sales data files and I'm having a hard time making the transition.
I know this is gone past the XLOOKUP question, but I appreciate any help and understand if you don't have the time to do so.
Thank you in advance.
July 16, 2010
December 1, 2020
July 16, 2010
Thanks for sharing your file. First, XLOOKUP; I don't see any issues with the XLOOKUP fomulas, so I'm not sure what your question is there.
In regards to data structure, the CropPlan data is in a tabular layout, so it looks fine for building dashboards from. I guess the next step is to either add the actual data to this table (using a different data type in column E for Crop Actuals), or build a separate equivalent table that tracks the actual data. If you choose the latter you will need to use Power Pivot to build a model that uses relationships between tables to compare the plan vs actual data.
Once you have some actual data in the file you can start to build your charts.