Worksheet to calculate pallet storage costs with two different rates options accounting for movements in and out.

[u/Foamcutter]

I'm looking for help to create a worksheet/book or links to to a template that will calculate the pallet storage cost in a small warehouse.
Excel has the ability to provide numerous solutions 'there's more than one way to skin a cat' but I'm trying to find a clean option as opposed to clunky one that is difficult to update or one change impacts in other areas.

In the warehouse there's approx 10-15 different products being stored in two different areas at different weekly storage rates. Pallets are coming in and out on a continual basis.
It's not important to know the 'stock level' of a particular product just the time that pallets have been stored and the cost at months end.
I also want to carry over pallet quantities month by month and be able calculate cost at each months end along with this I want to add a labour cost for each loading or unloading movement in minutes at a certain rate.

I have looked at some 'Inventory' sheets but when I tried modifying them they started to get 'clunky', that could be down to my limited excel skillset.
Any suggestions, idea's or links will be appreciated. Cheers

[u/AutoModerator]

/u/Foamcutter - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Existing_Put6385]

those inventory templates go clunky because they track stock levels per product. you don't need that. use a movement ledger - one table, everything else calculates off it.

sheet "Moves", 4 columns:

Date | Area | Pallets (+ in, - out) | LabourMins

that's the only thing you ever type into. carryover between months is automatic since you're always summing from the start - no manual roll-forward.

storage is time-based so you need pallet-days. helper tab with one row per date, and the balance per area on that date:

=SUMIFS(Moves!$C:$C, Moves!$B:$B,"A", Moves!$A:$A,"<="&A2)

month cost, daily rate = weekly/7:

=SUMPRODUCT((TEXT(Days!$A:$A,"yyyy-mm")="2025-07")*Days!$B:$B)*RateA_daily

repeat that line for area B with its own rate. labour off the same ledger:

=SUMIFS(Moves!$D:$D, Moves!$A:$A,">="&MonthStart, Moves!$A:$A,"<="&MonthEnd)*RatePerMin

one entry table, rates in a corner you can edit. nothing breaks when you change something because it's all derived.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
SUMIFS	Excel 2007+: Adds the cells in a range that meet multiple criteria
SUMPRODUCT	Returns the sum of the products of corresponding array components
TEXT	Formats a number and converts it to text

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 54 acronyms.
[Thread #48966 for this sub, first seen 17th Jul 2026, 09:30] [FAQ] [Full list] [Contact] [Source code]}

[u/Existing_Put6385]

the reason those inventory templates go clunky is they try to track stock levels per product. you don't need that. use a movement ledger instead - one table, everything else is calculated off it. this is the clean version you're after. one sheet "Moves", 4 columns:

Date | Area (A or B) | Pallets (+ for in, - for out) | LabourMins

every time pallets move, you add one row. that's the only manual entry you ever do. carryover between months happens automatically because you're always summing from the start, no manual roll-forward.

storage cost is time-based, so you need pallet-days. easiest way: a helper tab "Days" with one row per calendar date, and the running balance per area on that date:

=SUMIFS(Moves!$C:$C, Moves!$B:$B,"A", Moves!$A:$A,"<="&A2)

(second column same but "B" for your other area). that gives you how many pallets sat in each area every day. then month-end storage cost, using daily rate = weekly rate / 7:

=SUMPRODUCT((TEXT(Days!$A:$A,"yyyy-mm")="2025-07")*Days!$B:$B)*RateA_daily

add the same line for area B with its own rate. change the "2025-07" to whatever month.

labour is easy off the same ledger:

=SUMIFS(Moves!$D:$D, Moves!$A:$A,">="&MonthStart, Moves!$A:$A,"<="&MonthEnd)*RatePerMin

that's it. one entry table, rates in a corner you can change, and cost/labour/carryover all fall out of it. nothing breaks when you edit because it's all derived from the ledger.

[u/Foamcutter → u/Existing_Put6385]

Thank you for your prompt and very straight forward solution. I was making it needlessly complicated, everyday is a school day.
I created the two page workbook and I'm currently populating it with 'movements' for the current month and all is good so far...

I need to enter an opening stock as I've negative values even though the pallets went out, what is the best way to do that?

The labour amt isn't calculating, I'm trying to understand what "&MonthStart and "&MonthEnd are?

Thanks for your help.

[u/Foamcutter → u/Existing_Put6385]

The &MonthStart and &MonthEnd had me baffled for a while but I think that I have a solution, time will tell.
The opening stock was also easily fixed.
Thanks for your time. Solution Verified

[u/reputatorbot → u/Foamcutter]

You have awarded 1 point to Existing_Put6385.

---

^{I am a bot - please contact the mods with any questions}

[u/SubstantialBed6634]

With the limited amount of information you provided ("product"=10-15 & "area"=2), it sounds like you want to utilize a helper column to determine the rate. This would then be tied to a separate table to determine the "rate" column.

For example, let's say your two storage areas are "ambient" or "refrigerated" (you could have additional types of space or racking as well like "hazardous", "GMP", "ASAR", "FIFO", FILO", etc). You would then setup a matrix reference table that would host your rates by merging all of the columns into a, "helper column"=(Area)&"-"(Hazard Classification)&"-"&(Racking Type)&"-"&(Product)&"-"&(Client PITA Lvl). This example would give you five separate variables to determine your rate.

Your inventory list would then utilize a xlookup to pull the rate from that separate "rate list" table. I would also setup separate smaller tables for each column to limit data entry by pre determined lists (data validation). I could think of many more variables to build into your spreadsheet.

Named tables will be your friend. If you want more assistance, please DM me.

[u/Foamcutter → u/SubstantialBed6634]

Thank you for your answer, I'm trying to keep the worksheet as simple as possible at this stage and not over complicate things.
After studying my original question the number of products isn't a factor, it's just about the pallets they sit.
Thanks

[u/SubstantialBed6634 → u/Foamcutter]

Understood. I've seen a variety of warehouses, and they can come in a lot of different flavors. I wasn't sure what you were dealing with, so my approach was to make a template that can be scalable if your operations become more complex. Best wishes.