Information and Decision Tool 2

IST 309 – Winter 2020

Introduction:

The objective of this assignment is to provide the opportunity to learn and utilize new functionality in the development of a spreadsheet workbook and analyze the results in order to make a sound business decision.

Overview:

You are the Productions Manager responsible for the development of widgets, jobbers, and, thing-a-ma-jigs. Your company has enjoyed exclusive rights to supply these products for several years and it is your responsibility to ensure your customers continue to be happy with the products and services. Each of the products you produce has a different manufacturing team.

Your customer has determined their weekly needs for materials and provided you with a spreadsheet that is contained in the file “need em parts” (supplied by instructor). You have tracked your weekly deliveries and this information has been added to the file in the column titled “delivered”.

Requirements:

The customer has contacted your supervisor and expressed concern over the ability to meet their demand for materials. Your supervisor has requested you provide the following:

1) Perform an analysis of the number of Widgets, Jobbers and Thing-a-ma-jigs delivered from the beginning of the year until now. Calculate the following information, using cell arithmetic, and put it in the table of the provided workbook titled Analysis:

a) Total accumulated Orders and Delivered items for each product.

b) Calculate the difference between the total accumulated weekly orders and total accumulated weekly delivered items.

c) Change the format of the date field to be in the mm/dd/yy format.

d) All number fields should contain the “,” separator for numbers larger than 1,000.

2) Generate a single line chart (graph) to show the weekly difference for each of the products. Make sure your chart contains the following:

a) Chart on a separate worksheet titled “Difference Chart”.

b) Legend at the bottom with the names each product.

c) Proper axis labels (e.g., dates for the X axis).

d) Appropriate chart title.

3) Generate a written report describing containing the following:

a) Summary – Short explanation of the reason you are writing the memo.

b) Methodology – Explanation of the use of the spreadsheet and the steps used to analyze the data.

c) Findings – Conclusions drawn from analyzing the data.

d) Recommendations – Actions recommended to your supervisor.

4) Send the files (spreadsheet and memo) to the instructor’s email account –

mmosher@csusb.edu

.

5) Both files must contain your name in the filename (e.g., Mosher Tool 2)

Data

Orders Delivered Orders Delivered

85 73

62

52

63

73

71 62

52

64

85 73 52 59 71 63

59 74 82 85 59 52

100 63 66

100 59

74

83 75 74

63

52

85 72

44

63 80 59 45

62

52 80 100 94

73 73 82

74 73

74 72 66

52 72

63 61 83

82 61

45 81 52

66 81

61 63

83 61

75 44 76 62 75

44 62 75 63 44

67 62 73

94

86 73

82 73

75 64 81 64 66 72

73 73 76 68 83 41

59 72 82 59 52 72

61 66 58 63 45

59 81 83 58

94

73 61 70

62 73

82 58 87 73 73

66

71 72 63 76

83 55 85 61 81 56

52 56 59 81 87 67

63 82 100 61 71 56

81 55 74 75 85 76

87 80 52 44 59 56

71

82 62 100 70

85

83 73 74

59 61 52 64 52 57

100 64 63 63 82 68

74 68 44 75 87 59

52 68 62 44 71 58

Analysis

Delivered Total

Orders Total Delivered Total Difference Orders Total Delivered Total Difference

52

44

66

Sheet3