Excel. How much do you use it?

[jdpetey]

There are a lot of FREE online tutorials. I have considered paying a small fee to get a more in depth understanding. The ability to filter reports makes things so much easier. Creating visual charts is also a plus.

[Parts007]

I look for reasons to use Excel, hence this post. Attached is an example of a report I use frequently to track fast movers, in this case, cabin filters.

The answer of 2.8 circled in blue comes from the formula circled in red. Calculations happen in this order, Parenthesis, Exponents, Multiplication, Division, Addition and Subtraction.

FORMULA EXPLANATION:
= start of any formula
(C3+D3) = Being in parenthesis, Excel ads up the two cells, this case my QOH (13) + Amount i want to order (10) before any other calculation is done.

(F3/12) = F3 is my 12 month total sales history divided by 12 months. This results in number of filters we sell per month on average. (we don't see this answer)

Also the months supply numbers that are less than 1.0. Notice they are highlighted in red. This is a feature called Conditional formatting. Anything under 1.0 (or any number you set), it'll highlight to attract attention.

I show this not to brag about what I know, but to offer if you need something figured out, post it here and i can try to help.

Trust me the more you know about Excel, the more you realize how much you don't know.

[jdpetey]

I have been trying to improve on the conditional formatting and IF function. After the strike ended and GM allowed us to increase our RIM SPQ's, whilst still being protected, I increased the SPQ on some of the fastest movers and Direct Ship items that take longer to replenish. That being said, I have to come up with the most effective way to reduce the SPQ's at a later time when RIM may be lowering the SPQ"s of those very items.

I can send the complete SPQ list and export it and it will show RIM recommended SPQ along with increased SPQ. I would like to 'conditionally format' the sheet to highlight the Items I have modified for review.

[Parts007]

I have been trying to improve on the conditional formatting and IF function. After the strike ended and GM allowed us to increase our RIM SPQ's, whilst still being protected, I increased the SPQ on some of the fastest movers and Direct Ship items that take longer to replenish. That being said, I have to come up with the most effective way to reduce the SPQ's at a later time when RIM may be lowering the SPQ"s of those very items.

I can send the complete SPQ list and export it and it will show RIM recommended SPQ along with increased SPQ. I would like to 'conditionally format' the sheet to highlight the Items I have modified for review.

That's a great idea for a report, one I could use also. My first question is: Does your DMS (mine is R&R) have a field (FIELDA) for RIM suggested SPQ and a separate field for Dealer suggested SPQ (FIELDB). Easily create a report with the parameters if FIELDA does not equal FIELDB, and that's your list. From there you can fine tune it.

I will mess around with R&R to see if they offer such a data field

[jdpetey]

CDK here. I am not aware of anywhere in part info that would show an accurate equivalent of the RIM SPQ. The BSL (Best Stocking Level) is about as close as one could get. A long time ago, it appeared to be close, but I found inaccuracies between the RIM SPQ and BSL. Filtering my ‘Daily Inventory Snapshot’ by State Code 02 provides a list of currently managed parts that I can sort by fastest moving to calculate per se a 3 month supply to reduce the frequency with which we have to constantly put away the same parts. I just want to develop a way to back off higher levels in conjunction with declines in RIM SPQ’s as well as sales.

[jdpetey]

From the complete SPQ list,I was able to find a way to use the IF function and create a true/false column for the Current SPQ vs RIM SPQ and isolate all the occurrences where the current SPQ was higher than the last RIM SPQ. From that point, I had to go in to RIM and temporarily exclude those numbers, briefly, and then remove the temp exclusion for them to recalculate to the RIM, or desired, SPQ levels for tomorrow.

[Parts007]

From the complete SPQ list,I was able to find a way to use the IF function and create a true/false column for the Current SPQ vs RIM SPQ and isolate all the occurrences where the current SPQ was higher than the last RIM SPQ. From that point, I had to go in to RIM and temporarily exclude those numbers, briefly, and then remove the temp exclusion for them to recalculate to the RIM, or desired, SPQ levels for tomorrow.

nice!

[Stevenspeaking]

Chrysler allowed us to "special" order 10% of our monthly purchases with out having to pay the handling charge. However there wasn't anywhere to see what was used or available. I kept this as a guide to see if I was close to getting charged or could upgrade some orders without worry. Note the 20% was added when they had supply chain issues and gave dealers an extra 10% grace.

[AIIVESINCE85]

I use it during month end recon. Using formulas mixed of IF, VLOOKUP & CONCAT to match invoice numbers & accounts to the MFG statements. Really to locate and fix missing MFG paperwork or incorrect entries in accounting. Makes the controller happy!