ITECH1100 Assignment 1 - Sock Life: Data Analysis and Automation

This assignment utilizes Microsoft Excel to automate business processes for the fictional company, Sock Life, a custom sock manufacturer. The student calculates the annual operational costs, including employee salaries, and visualizes quote amount and manufacturing cost variations over time using charts. A pivot table and chart are created to analyze the popularity of different sock sizes and materials, displaying mean, standard deviation, and quote amounts. The student then improves the process with a swim lane diagram, allowing staff to select materials and sizes, with the software automatically displaying the average quote amount. Finally, the process is further enhanced to determine item popularity based on historical data and stock status, generating manufacturing instructions based on quote acceptance and inventory levels. The assignment demonstrates the application of data analysis and automation techniques to improve business efficiency and decision-making.

Running head: SOCK LIFE
SOCK LIFE
Name of the Student
Name of the University
Author Note

Running head: SOCK LIFE
Task 0:
In this assignment the automated solutions for the employees of a company names as Sock
life is produced by using Excel. The Sock life is known to be a small company that produces
cloths and mainly specialized for creation of custom one of a kind socks and the products are
sold through an online store. The team members of Sock Life are Anne, Benjamin and
Cheryl. Anne and Benjamin are mainly specialized for manufacturing socks and Cheryl
specialized for managing sales, orders and delivery.
Name Working days in the
week
Working hours(on
weekly basis)
Additional Working
hours(in week)
Anne Monday, Tuesday and
Wednesday
22.5 3
Benjami
n
Thursday and Friday 15 0
Cheryl Every weekday 22.5 3
Task 1:
The annual cost for operation is calculated in excel including the annual salary of the
employees using suitable formulas as given below. For calculating annual salary of
employees the weekly wage is multiplied by 52.
Name Working
days in a
week
Total
Working
hours(per
week)
Additional
Working
hours(per
week)
Total
Working
hours (per
week)
Hour
ly
rates(
in $)
Annu
al
cost(i
n $)

Running head: SOCK LIFE
Anne Monday,
Tuesday and
Wednesday
22.5 3 25.5 26 3447
6
Benjamin Thursday and
Friday
15 0 15 26 2028
0
Cheryl Every
weekday
22.5 3 25.5 24 3182
4
Employee as
replacement
of Cheryl
66 39 1029
6
Fixed cost in
a year(in $)
9100
Utility cost
in a year(in
$)
3120
Total
Outgoing
cost in a
year(in $)
109096
Thus as calculated by excel the total outgoing cost of the Sock life is $109096.
Task 2:

Running head: SOCK LIFE
The amount of quote amount has been varied over time and manufacturing cost is varied over
time which are depicted by the following two charts.
2018-06-04 2018-06-20 2018-07-06 2018-07-24 2018-08-06 2018-08-21 2018-08-31 2018-09-14 2018-09-27
0
100
200
300
400
500
600
700
800
900
Quote amount Variation over time
Date
Quote amount(in $)

Running head: SOCK LIFE
2018-06-04 2018-06-19 2018-07-03 2018-07-20 2018-08-01 2018-08-15 2018-08-28 2018-09-05 2018-09-19 2018-10-02
0
20
40
60
80
100
120
140
160
180
Manufacturing cost ($)
Date
Manufacturing Cost(in $)
Now, to obtain which size and material has most popularity, variation and average value in
the historical year a pivot table with pivot chart is created in excel and this is shown below.
Thus in the pivot chart mean, standard deviation and quote amount of item types are
displayed as shown below.

Running head: SOCK LIFE
CottonL
CottonS
CottonXS
LeatherL
LeatherS
LeatherXS
SilkL
SilkS
SilkXS
TweedL
TweedXS
WoolM
WoolXL
WoolXXL
0
100
200
300
400
500
600
Average of Quote amount
StdDev of Quote amount
Count of Quote amount
It can be easily obtained from the chart that the category SilkXXL has the maximum SD and
hence this particular category has been varied mostly from staff to staff. The item SilkS has
the most average price in the historical year. The item WoolXS and WoolXL and LeatherXS
are mostly sold in the historical year.
Task 3:
Now, the process is improved by the staff members and the process plan for improvement is
shown in the following swim lane diagram. The items can be chosen by staff members for
different item size and material as per the requirement of different customer and the quote
amount of the specific category is displayed automatically to the customer. The quote amount
displayed is the average quote amount of that particular category (combination of material
and size) in the historical year.

Running head: SOCK LIFE
Sample run:
Material Size
Leather XL Automatic display to staff members
Quote Amount(in $)
160.1285714
For Staff members' Usage

Running head: SOCK LIFE
Task 4:
Now, the process is further improved where staff members will have the option to choose the
material, size and whether the quote is accepted by the customer or not, then the software will
automatically display the item is popular or not popular and the quote amount. Additionally it
is shown that whether the item is in stock or not. Here, the popularity of an item is
determined by whether the item has more than the 9 times in the historical year i.e. the item is
popular if the count in historical year is more than or equal to 9, otherwise the item is
unpopular. The item inventory is randomized with in stock or not in stock as the inventory of
the item is unknown. The manufacturing instruction (start or do not start manufacture)
depend on item quote acceptance and stock status. If the quote is accepted and stock status is
not in stock then start manufacture instruction is displayed.
Sample run:
Material Size Quote Acceptance by customer
Silk XS Not Accepted
Popularity Quote Amount(in $) Stock Status Manufacture Item
Unpopular 413.9857143 Not in Stock Do not Start Manufacture
To be used by Staff members
Automatic Display to Staff Members