help creating a repricing tool with defined parameters

[jayfresh]

Hey Experts!

I'm trying to create a repricing tool which will provide prompts for repricing based on defined parameters.

We currently have over 2000 skus listing on various marketplaces at different profit levels.

Using the example attachement I need the user to easily identify skus to reprice with potential scenarios:

Stk weeks left is below X and country X profit is below X then increase selling price

Stk weeks left is above X and country profit is above X then lower selling price

If country X achieves X sales and country X profit is below X then increase selling price

If country X is below X sales and Country X profit is above X andstk weeks left is above X then lower selling price

So basically the aim is to maximise profit in all countries when stock weeks is low/or reaches certain defined sales i.e 30 a month and to lower prices when stock weeks is high.

Hope this makes sense! appreciate any help.

THanks

Jay

[paulked]

Hi and welcome to the forum.

What you are asking is a bit vague without examples. For instance, increase/decrease prices by how much? The example file you have posted doesn't even contain prices!

I noticed you haven't started any VBA code yet either, do you want someone to write the code for you or are you looking for a starting point?

[paulked]

You have cross-posted here:

https://www.mrexcel.com/forum/excel-...ight=repricing

Please read these rules http://www.vbaexpress.com/forum/faq...._new_faq_item3

[jayfresh]

hi, thanks for the reply.

Yes, i'm looking for a starting point (completely new to VBA)

Ideally it would be based on a min and max profit level. The sales, stock, prices, and profits will be vlookups from the main inventory spreadsheet. This sheet will ideally be a trigger to reprice based on the stock level to maximise the profit.

Sorry for the cross post.

Thanks for any help!

[SamT]

ooking for a starting point (completely new to VBA)

First Step: State your business Rules

• Stk weeks left is below X and country X profit is below X then increase selling price
• Stk weeks left is above X and country profit is above X then lower selling price
• If country X achieves X sales and country X profit is below X then increase selling price
• If country X is below X sales and Country X profit is above X and stk weeks left is above X then lower selling price

Second Step: Describe your Data
Examples
Sheet1

• Stk Weeks Left in , Column A
• Stk Weeks Left lower limit in Column B
• Stk Weeks Left upper limit in Column C
• Country Profit in Column D
• Etc

Sheet2

• Country Sales in Column A
• Country Sales Lower Linit in Column B
• Country Sales Upper Limit in Column C
• Etc

Third Step: Refine your Business Rules (yes you will see things you didn't before)

Fourth:
Create a road map of when and where to look at and compare data
Examples

1. Go down the list of Stock Items on sheet3 in column A
2. For each Item,

• look at the Weeks of Stock left in Column B
  
  
• Look at the Company Profit , Column C on Sheet4
  
• Look at the Company Sales numbers on sheet 5 column F
  PLace Raise/Lower Price notifications in Sheet 1, Column X
  

Fifth:
Rearrange the data so the code can use it better.

Sixth: Ask the Experts at VBAExpress what to do next.

[jayfresh]

thanks for the response sam, i will get cracking and reply back soon