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]

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]quotes[/TD]
[TD]sales[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week1[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week2[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week3[/TD]
[TD="align: right"]81[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week4[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]51[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week5[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]47[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week6[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week7[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week8[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]39[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week9[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]43[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week10[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]52[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week11[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]55[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week12[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]49[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week13[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]48[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week14[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]51[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week15[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week16[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]43[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week17[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week18[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]44[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week19[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]41[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]week20[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]52[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]I plotted this on a scatter chart [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]suggests that as you achieve more and more quotes[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]sales do not rise at the same rate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]