Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.

Get a Professional Answer

Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

100% Satisfaction Guarantee

Rate the answer you receive.

Ask NewITZone Your Own Question

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!

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.

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?

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

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.

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.

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