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-questions/1040184-help-automate-repricing-tool-using-if-statements.html?highlight=repricing

Please read these rules http://www.vbaexpress.com/forum/faq...._new_faq_item3 (http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#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

Go down the list of Stock Items on sheet3 in column A
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