Newbie here - pulling out my hair trying to figure this.
I am still an intermediate in Excel so hoping I can explain myself well
I am trying to see if its possible to calculate an estimated cost of storing an item based on
1) how many units
2) Cost per day
3) estimate time it will be there - factoring in sales (ie as we sell an item, the overall cost per day will reduce)
Situation is
We want to send 10 units of stock to FBA. FBA charge a fee per item per day (its actually per m2 but for simple sake - lets say per item)
So as a basic guide this will be 1p per item per day
We estimate we will sell all units inside 30 days on a sliding scale. This works out at 1 every 3 days approx
So for Day 1 we would get charged 10p
Day 2 - 10p
Day 3 - 10p (we sell one)
Day 4 - 9p (as we now have 9 left)
Day 5 - 9p
Day 6 - 9p (we sell 1)
Day 7 - 8p
Etc
Working it on a piece of paper/visually, at this estimated rate we would get charged about 1.64 for the whole time that stock has sat there inside our estimated 30 days
I just cannot translate that to a workable formula where the values for
Units (10)
Days (30)
Cost (1p)
Can be changed and formula works dynamically (Ideally want to be able to have a forumla to work it all and contained in 1 cell - so we can then work it into our other costings for this stock - but I doubt its doable)
I have googled and not even found anything that works or gets near to it!!
have attached an image of my long handed visual working out - XL2BB wont install at the moment!!
Hope I have explained myself well.
Thank you in advanced.
Hopeful for a solution -but might need to find a longer way to do it
I am still an intermediate in Excel so hoping I can explain myself well
I am trying to see if its possible to calculate an estimated cost of storing an item based on
1) how many units
2) Cost per day
3) estimate time it will be there - factoring in sales (ie as we sell an item, the overall cost per day will reduce)
Situation is
We want to send 10 units of stock to FBA. FBA charge a fee per item per day (its actually per m2 but for simple sake - lets say per item)
So as a basic guide this will be 1p per item per day
We estimate we will sell all units inside 30 days on a sliding scale. This works out at 1 every 3 days approx
So for Day 1 we would get charged 10p
Day 2 - 10p
Day 3 - 10p (we sell one)
Day 4 - 9p (as we now have 9 left)
Day 5 - 9p
Day 6 - 9p (we sell 1)
Day 7 - 8p
Etc
Working it on a piece of paper/visually, at this estimated rate we would get charged about 1.64 for the whole time that stock has sat there inside our estimated 30 days
I just cannot translate that to a workable formula where the values for
Units (10)
Days (30)
Cost (1p)
Can be changed and formula works dynamically (Ideally want to be able to have a forumla to work it all and contained in 1 cell - so we can then work it into our other costings for this stock - but I doubt its doable)
I have googled and not even found anything that works or gets near to it!!
have attached an image of my long handed visual working out - XL2BB wont install at the moment!!
Hope I have explained myself well.
Thank you in advanced.
Hopeful for a solution -but might need to find a longer way to do it
