Predicting sales figures from quotes

[bizzare187]

Hi all

Probably quite a basic question for you experts on here but I'm trying to solve the following problem:

I have values of quotes and values of sales for c.30 weeks worth of data:

Example,
Week 1 Quotes: £3m Sales: £1m
Week 2 Quotes: £4m Sales: £2m etc.

I'm trying to work out a formula to predict what sales are going to be in week 4 (for example) based on value of quotes received in week 1 (i.e. if quotes is £x in week 1, sales are £y in week 4).

Note I'm not sure exactly the time frame for converting quotes to sales so would also like to do some kind of calculation (possibly correlation / regression - not sure?) which shows which timeframe is most accurate (i.e. is there a stronger correlation between week 1 quotes / week 3 sales or week 1 quote / week 4 sales).

Any help you folks could provide would be massively appreciated.

Many thanks.

 

[oldbrewer]

I would do a scatter plot, use my eyes to look at it and make a best guess - the word predict is the clue - as you build up more and more weeks of data it will get better - but beware seasonality,,,,,,,,,,,,,,,,,,,,,

with 30 weeks of data find the slope and intercept as a start,,,,,,,,,,,,

 

[bizzare187]

I would do a scatter plot, use my eyes to look at it and make a best guess - the word predict is the clue - as you build up more and more weeks of data it will get better - but beware seasonality,,,,,,,,,,,,,,,,,,,,,

with 30 weeks of data find the slope and intercept as a start,,,,,,,,,,,,

Thanks for your response.

Would a scatter plot give an idea of value of sales for a particular week based on value of quotes from previous weeks? What would be the x and y axis?

 

[oldbrewer]

you plot quotes versus sales and hopefully you will see a straightish or a curvedish trend choose X-Y plot with no line to start with

 

[shg]

Cross-posted at https://www.excelforum.com/excel-fo...predicting-sales-from-quotes.html#post5052374

 

[oldbrewer]

			quotes	sales
		week1	75	40
		week2	66	45
		week3	81	39
		week4	63	51
		week5	70	47
		week6	71	45
		week7	75	50
		week8	88	39
		week9	80	43
		week10	78	52
		week11	61	55
		week12	69	49
		week13	70	48
		week14	66	51
		week15	77	40
		week16	88	43
		week17	66	50
		week18	60	44
		week19	75	41
		week20	77	52
		I plotted this on a scatter chart
		suggests that as you achieve more and more quotes
		sales do not rise at the same rate

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>