• 100% Satisfaction Guarantee

NewITZone, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 90
Experience:  I have been using Office Pro for 10 years and implemented applications for business use.
53509759
Type Your Microsoft Office Question Here...
NewITZone is online now

# I need a formula that will add months of sales and a

### Customer Question

I need a formula that will add months of sales and a fraction of one of the months sales. For inventory management and turn. Say I have sales data from all 12 months last year. I am shooting for a 1.8 inventory turn. 12months/1.8 is 6.67.
Say I am starting in June.
So, I need to add six months of sales plus .67 of the seventh month to determine targeted BOM inventory.
In need this to work for all 12 months of the year and convert turn to months of data.
It is sequential because all of the different months are forecasted to have different sales, so june=July-Dec and part of Jan and July=Aug-Jan and part of Feb
Thanks!
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  The-PC-Guy replied 1 year ago.

can you send an example?

Customer: replied 1 year ago.
I am not sure how. I could send you my spread sheet as it is?
Expert:  The-PC-Guy replied 1 year ago.

that would be great

Expert:  The-PC-Guy replied 1 year ago.

www.ge.tt

www.wikisend.com

Customer: replied 1 year ago.
Customer: replied 1 year ago.
starting at M8. and using COGS numbers, which are projected sales at whole sale.I want the formula to link to the drop down box below"Choose Targeted Inventory turn" so I can see how on hand inventory changes with inventory turn.
Expert:  The-PC-Guy replied 1 year ago.

i dont see a drop down box, and formulas don't usually link to those anyway.

Lets assume that I don't know any terminology COGS for example.

Can you give me in Column M if that is where you want your number what the expected numbers would be if you did the math manually? And how you derive those numbers mathamaticlaly?

Expert:  The-PC-Guy replied 1 year ago.

try if possible to refer to cells or numbers, are you taking numbers from column D for example?

Customer: replied 1 year ago.
Sure. So COGS is cost of goods sold or wholesale cost. Lets assume I want a 1.8 inventory turn. That means I sell through my inventory completely 1.8 times in a year. Since there are 12 months in a year, 12/1.8= 6.67. So at the end of each month I should have 6.67 months of inventory on hand.
At the end of June I should have SUM(D9-D14)+(.67(D15)) OR 6.67 months of inventory which is (942807.6)+(82134.50)=1,024942.10
Customer: replied 1 year ago.
67*d15)
Customer: replied 1 year ago.
Click on the box with the number 1.7 on it and the drop down list will appear to the right
Expert:  The-PC-Guy replied 1 year ago.

so it varies based on that number?

Customer: replied 1 year ago.
I would like to be able to change the targeted turn rate to see what the numbers do.
Customer: replied 1 year ago.
You can see at G 27 and H27 I have made a table showing the turn rates in the drop down box and what they convert to in months of inventory.
Expert:  The-PC-Guy replied 1 year ago.

well honestly at this point I would recomend a macro be used to calculate and populate the cells in M column. Basically the macro could be made to fire off everytime you change the value in that cell. I could probably devise a formula to do this, but it would be a very complicated formula.

There would be a slight additional charge to create a macro because it is a bit complicated, but I think it can be done.

Customer: replied 1 year ago.
Of course I would like to solve the problem, but what is a "slight additional charge?"
Expert:  The-PC-Guy replied 1 year ago.

total cost would be \$99

Customer: replied 1 year ago.
I am not yet prepared to pay that amount. I think it can be done with a standard formula. Can I re-open this case/question if I choose too?
Expert:  The-PC-Guy replied 1 year ago.

standard formula yes, It can be done, but extremely complicated, a formula would actually take longer then a macro and would not be as efficient.

Keep in mind the following

1. This is how I make a living.

2. The price also includes troubleshooting, if for some reason you cannot run the macro or you get errors, or it doesn't quite work right.

3. The price was arrived at based on my experience which is extensive, this is why I know enough to recommend a macro instead of a formula, I know how excel works and works best.

Customer: replied 1 year ago.
I have the contact info and will consider it again tomorrow. Gotta go for tonight. Thanks
Expert:  The-PC-Guy replied 1 year ago.

ok

Customer: replied 1 year ago.
Hello,I saw that my credit card was charged \$43.00. I am happy to pay the \$5.00 appointment fee, but the question was not answered. Please refund me the \$43.00 today.
Thank You
Charles