Function to calculate Inventory carrying cost/fees

Question

Hello-&nbsp;&nbsp;I've researched this but have been unable to come up with a solution to this problem.&nbsp;I am trying to find a function (or formula) that can calculate the total carrying cost of a set amount of inventory given a daily rate of sale, and daily storage cost per unit.&nbsp; I can do this by creating a separate table but I need a function/formula as I have many rows of items and it would not make sense to create a table for each item.&nbsp;Data available:&nbsp;&nbsp;&nbsp;Units0.133333Daily Units Sold$ Cost:0.0029Storage cost/fee- per unit per DayQty92Curr on handDays690Curr days on hand&nbsp;Tring to solve for:$ Val:XXXTotal cost/fees to exhaust all inventory&nbsp;Thanks in advance for any thoughts or direction-&nbsp;-DK&nbsp;&nbsp;

peterbartholomew1 · Answer

cool2021&nbsp;Using Excel 365 you could work out the cost of stock for each day and Sum it= LET(
  stock,Units+SCAN(Qty,SEQUENCE(Days),
      LAMBDA(acc,d, acc - Units)),
  SUM(stock*Cost))Otherwise you could recognise the amounts as forming a arithmetic sequence and use the formula (average of first and last multiplied by the number of intervals)= (Days+1)*Cost*Qty/2&nbsp;

cool2021 · Answer

PeterBartholomew1&nbsp;a possible solution. But why make a busy business user or analyst enter formulas when they could instead click one button (as in my template example) and everything just happens for them automatically?

sergeibaklan · Answer

cool2021&nbsp;
Good question. Usual issues with third-party solutions
1) Vendor stops support for any reason (e.g. it doesn't work in updated environment) and you have to start from scratch
2) You need some modifications from time to time and here you fully depends on vendor
Each customer decides what is better. Invest some time in its own solution or rely on vendor. Both work.

cool2021 · Answer

Right. And so as long as Excel Desktop is installed (that narrows it down to millions of users) and their data resides in an SQL database locally, network or in the Cloud and/or if their data resides in flat files somewhere locally, on a network or in the Cloud, then my templates will work.

Forum Discussion

Function to calculate Inventory carrying cost/fees

4 Replies

Resources


Units	0.133333	Daily Units Sold
$ Cost:	0.0029	Storage cost/fee- per unit per Day
Qty	92	Curr on hand
Days	690	Curr days on hand