help with double counting please

Si Spence

Dear clever people of planet SS,

 

please see SS:

https://app.smartsheet.com/b/publish?EQBCT=1d447b4d658d488bacd135960036171e

 

in this example, i have a customer who has asked to see four flavour varients of the same type of product, from which they will chose just one that will launch in their store.  The volume forecast for this one line is 400,000 units with a sales value of £1.2m.

 

The challenge i have in reporting the size of the value and volume of the opportunity is that the the total volumes and values across all four lines are being assumed as the total opportunity ie 1.6m units and £4.8m sales.  This is not the case as only one product will actually be chosen to take forward to launch.    what i dont want to do is simple divide the opportunity by 4 ie 100k units and £300k sales because then once i have killed off the three lines not progressing I am left with one line where the volume and value of teh opportunity are being undervalued. similarly i dont want to leave any cells blank.

 

If this makes sense to anyone, then i'd appreciate any thouhts as to how i might manage this?

 

Thank you   Si.

Tony Davidson - SmartSheet Certified User 2016

What is your end goal with this?  I'm not clear what you're trying to achieve.  Tony

Si Spence

Hi Tony, thanks for taking an interest. I want to summarise all opportunities, probably in Sights. in this case the opportunity is 1.2m not 4.8m. But if I were to create a total at the bottom of the sheet I would be overstating the business potential X 3 times. 

 

any clearer?

 

we often receive briefs from customers wanting us to show several flavour variants of which they may choose just one or two. If we were to value each variant at 100% of the customer forecast then we would over commit to the business. Conversely if we were to split the forecast across all varients then as we refine the offer down to the last one or two then we could undervalue the offer. 

 

Maybe some sort of 'what if' which might enable me to only count the business value once, or as many times as flavours that we end up with. 

 

Im not sure I'm making this any clearer??

 

 

 

 

Tony Davidson - SmartSheet Certified User 2016

Could you just create four total options to show their optional totals then in Sights?

Si Spence

Hi Tony,  not really because whilst I've used four as an example we might show a customer just two flavour options or 10 options knowing that they will funnel down to just one. 

 

The value and volume of the business opportunity remains the same. I'm thinking I need a formula that says that if all the 'product' are the same then just use one of the values. Perhaps a lookup adding up all the same 'product' and then taking an average?

 

thanks again. 

Greg Gates

I'm not positive I understand your situation, but if you're looking to just calculate the value for a single product regardless of the number of variations, you could do something like this (assuming the same values have been entered for each variation): 

 

In Volume:

=SUMIF(Position1:Position4, "Joints",  Volume1:Volume4) / (COUNTIF(Position1:Position4, "Joints"))

 

In Value:

=SUMIF(Position1:Position4, "Joints",  Value1:Value4) / (COUNTIF(Position1:Position4, "Joints"))

 

Does that help? Or am I off the mark here?

J. Craig Williams

Si,

 

Did you get your solution?

The publish link is gone, so I can not add anything beyond to say that in Greg's solultion, you can change "Joints" to a cell reference to increase flexibility.

 

Craig