(Solved)Production planning with Excel Suppose your manager as
Production planning with Excel
Suppose your manager asks you to create a spreadsheet to compute a production schedule. Your schedule should stipulate a production quantity for seven products that is based on sales projections made by three regional managers at your company’s three sales regions.
Create a separate worksheet for each sales region. Use the data in the WORD file attached. The file contains each manager’s monthly sales projections for the past year, actual sales results for those same months, and projections for each month in the coming quarter.
Create a separate worksheet for each region’s data. Import the data from Word into Excel.
On each of the worksheets, use the data from the prior four quarters to compute the discrepancy between the actual sales and the sales projections. This discrepancy can be computed in several ways: You could calculate an overall average, or you could calculate an average per quarter or per month. You could also weight recent discrepancies more heavily than earlier ones. Choose a method you think is most appropriate. Explain why you chose the methods you did.
Modify your worksheets to use the discrepancy factors to compute an adjusted forecast for the coming quarter. Thus, each of your spreadsheets will show the raw forecast and the adjusted forecast for each month in the coming quarter.
Create a fourth worksheet that totals sales projections for all of the regions. Show both the unadjusted forecast and the adjusted forecast for each region and for the company overall. Show month and quarter totals.
Create a bar graph showing total monthly production. Display the unadjusted and adjusted forecasts using different colored bars.
Region 1 2013 Sales Summary
Product 1
Product 2
Product 3
Product 4
Product 5
Product 6
Product 7
Jan, 2013
Forecast Sales
10
20
5
10
20
5
34
Actual Sales
12
21
5
12
21
5
36
Feb, 2013
Forecast Sales
10
22
5
10
22
5
35
Actual Sales
11
23
6
11
23
6
40
Mar, 2013
Forecast Sales
11
22
5
11
22
5
41
Actual Sales
11
22
4
11
22
4
45
Apr, 2013
Forecast Sales
12
24
12
12
24
12
47
Actual Sales
13
25
13
13
25
13
50
May, 2013
Forecast Sales
12
24
12
12
24
12
49
Actual Sales
11
23
13
11
23
13
54
Jun, 2013
Forecast Sales
12
23
15
12
23
15
50
Actual Sales
12
24
16
12
24
16
56
Jul, 2013
Forecast Sales
13
23
18
13
23
18
52
Actual Sales
15
24
20
15
24
20
56
Aug, 2013
Forecast Sales
13
24
18
13
24
18
57
Actual Sales
15
24
20
15
24
20
60
Sep, 2013
Forecast Sales
14
23
16
14
23
16
59
Actual Sales
15
25
16
15
25
16
59
Oct, 2013
Forecast Sales
15
24
12
15
24
12
61
Actual Sales
16
26
11
16
26
11
62
Nov, 2013
Forecast Sales
15
25
8
15
25
8
65
Actual Sales
16
27
8
16
27
8
66
Dec, 2013
Forecast Sales
14
26
6
14
26
6
60
Actual Sales
15
28
5
15
28
5
61
Region 2 2013 Sales Summary
Product 1
Product 2
Product 3
Product 4
Product 5
Product 6
Product 7
Jan, 2013
Forecast Sales
12
19
6
9
20
4
35
Actual Sales
13
22
3
11
22
6
35
Feb, 2013
Forecast Sales
13
23
6
10
22
5
37
Actual Sales
12
24
7
13
24
7
40
Mar, 2013
Forecast Sales
12
21
5
10
21
6
39
Actual Sales
12
24
6
14
21
5
43
Apr, 2013
Forecast Sales
14
23
13
11
25
13
47
Actual Sales
13
21
12
12
23
14
53
May, 2013
Forecast Sales
14
25
13
11
25
14
49
Actual Sales
12
22
14
10
23
13
50
Jun, 2013
Forecast Sales
15
26
14
13
24
15
51
Actual Sales
10
24
16
14
26
17
55
Jul, 2013
Forecast Sales
15
22
17
12
25
17
52
Actual Sales
14
24
21
14
25
21
56
Aug, 2013
Forecast Sales
15
23
18
13
26
18
55
Actual Sales
15
25
19
17
24
21
59
Sep, 2013
Forecast Sales
16
21
19
15
26
16
58
Actual Sales
16
23
15
13
24
17
57
Oct, 2013
Forecast Sales
18
25
13
15
23
13
61
Actual Sales
17
23
12
15
26
12
60
Nov, 2013
Forecast Sales
17
27
10
16
24
10
65
Actual Sales
17
25
7
16
25
10
66
Dec, 2013
Forecast Sales
15
27
7
17
25
5
63
Actual Sales
15
27
6
14
28
6
60
Region 3 2013 Sales Summary
Product 1
Product 2
Product 3
Product 4
Product 5
Product 6
Product 7
Jan, 2013
Forecast Sales
10
20
5
10
20
5
34
Actual Sales
12
21
5
12
21
5
36
Feb, 2013
Forecast Sales
10
22
5
10
22
5
35
Actual Sales
11
23
6
11
23
6
40
Mar, 2013
Forecast Sales
11
22
5
11
22
5
41
Actual Sales
11
22
4
11
22
4
45
Apr, 2013
Forecast Sales
12
24
12
12
24
12
47
Actual Sales
13
25
13
13
25
13
50
May, 2013
Forecast Sales
12
24
12
12
24
12
49
Actual Sales
11
23
13
11
23
13
54
Jun, 2013
Forecast Sales
12
23
15
12
23
15
50
Actual Sales
12
24
16
12
24
16
56
Jul, 2013
Forecast Sales
13
23
18
13
23
18
52
Actual Sales
15
24
20
15
24
20
56
Aug, 2013
Forecast Sales
13
24
18
13
24
18
57
Actual Sales
15
24
20
15
24
20
60
Sep, 2013
Forecast Sales
14
23
16
14
23
16
59
Actual Sales
15
25
16
15
25
16
59
Oct, 2013
Forecast Sales
15
24
12
15
24
12
61
Actual Sales
16
26
11
16
26
11
62
Nov, 2013
Forecast Sales
15
25
8
15
25
8
65
Actual Sales
16
27
8
16
27
8
66
Dec, 2013
Forecast Sales
14
26
6
14
26
6
60
Actual Sales
15
28
5
15
28
5
61
Region 1 2014 Sales Forecasts
Product 1
Product 2
Product 3
Product 4
Product 5
Product 6
Product 7
Jan, 2014
14
24
8
15
24
12
61
Feb, 2014
15
25
8
15
25
12
65
Mar, 2014
16
26
8
14
26
15
70
Region 2 2014 Sales Forecasts
Product 1
Product 2
Product 3
Product 4
Product 5
Product 6
Product 7
Jan, 2014
14
24
8
15
24
12
61
Feb, 2014
15
25
8
15
25
12
65
Mar, 2014
16
26
8
14
26
15
70
Region 3 2014 Sales Forecasts
Product 1
Product 2
Product 3
Product 4
Product 5
Product 6
Product 7
Jan, 2014
14
24
8
15
24
12
61
Feb, 2014
15
25
8
15
25
12
65
Mar, 2014
Expert Answer
Answer to Production planning with Excel
Suppose your manager as. . .
OR