Urgent nesting calculation issues

Hey all!

I've got an issue I'm trying to wrap my head around. At the moment we manually do all of these calculations, but if we could create a formula to do this it would definitely streamline our process!

At my work we have a spreadsheet we use to calculate estimated current prices of our supplies. This is based mostly off of quantity, and years since last order.

The way it works is we have a Historical (last used) price and quantity. from there we adjust it to reflect a more accurate current price.

In general, every year our supplies get 3% more expensive, so the further back our last order was, the more we'd have to compound that 3%. We round our years, so if the last order was placed on 6/7/16 then as of 5/2/18 it's still only counted as 1 year.

Furthermore quantity ordered plays a huge part, for every time we order double from the last order, we get a 7% Discount. So if our last order was 10, and now we're buying 80, that would be a discount of 21%.
Conversely, every time we order half our last order, we are charged an extra 7%

What I have to work off of is: Historical Price, Historical Quantity, and Targeted Order Quantity. Trying to estimate the appropriate Current Price taking into account the 3% annual increase, and 7% increase or decrease depending on order quantity.

Any help is greatly appreciated,

Thanks in advance!!!!

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Probably an attachment with some representative examples and some expected results would be a big help.

Hi asills03. Welcome to the forum.

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

a screen snapshot isn't really helpful, people still have to guess at what discounts you are applying and under what conditions. An actual sheet with actual examples (desensitized) will help others to see under what conditions you are applying what discounts.