Hi Doug,

Can you please supply your workbooks so we don't have to recreate everything.

The IF in your 2nd image doesn't have any logical test?  And I can't tell what cell that formula is in.

regards

Phil

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.

Doug

Hi Doug,

No files were attached. Please upload again and be sure to click the 'Start Upload' button after selecting your file.

Mynda

Don't know what happen, it showed the attachment, let me try again.

I see what I did, I dragged it over but did not upload it.  Sorry.

Hi Doug,

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.

Mynda