### (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