ITECH1100 Assignment 1: Process Automation for Uniform Zombie

This assignment focuses on automating the business processes of Uniform Zombie, an online clothing store. The student utilizes Excel to calculate operational costs, analyze sales data through various charts (distribution of quote values, manufacturing cost by size, profit by size, and profit share by employee), and automate the quoting process. The automation includes a table that allows employees to select material and size, generating a quote using the AVERAGEIFS formula. The assignment also addresses production decisions by incorporating factors like popularity and stock availability to optimize resource usage and minimize waste. The solution demonstrates the application of Excel formulas like IFERROR to handle missing data and inform production decisions, aiming to improve efficiency and profitability within the organization.

Running head: PROCESS AUTOMATION FOR UNIFORM ZOMBIE
Process automation for Uniform Zombie
Name of the Student
Name of the University
Authors note

1PROCESS AUTOMATION FOR UNIFORM ZOMBIE
Task 0
Uniform Zombie is an online store that manages it clothing manufacturing and sales
business with the help of the Abbey, Belle, and Constance. Abbey and Belle are skilled
manufacturing staff whereas Constance is part time worker and manages the order of the
products, sales as well as delivery.
Task 1
In the accompanied excel total operational cost is calculated. In this calculation the
total payable to three employees as well as the replacement workers, insurance cost, utility
cost for the year are also considered. In order to calculate the total payable to the employees
standard 5 day/week work days are considered.
Employe
e Shift Days
Da
y
co
un
t
Shi
ft
Ho
urs
Total
Shift
hours
every
week
social
media
prese
nce
Total
Work
ing
Hour
Paya
ble/
Hour
Weekl
y
Payab
le
Yearly
Payabl
be
Abbey
Mondays,
Tuesdays
and
Wednesda
ys 3 7.5 22.5 3 25.5
$
26.00
$
663.00
$
34,476
.00
Belle
Thursdays
and
Fridays 2 7.5 15 15
$
26.00
$
390.00
$
20,280
.00
Constanc
e
every
weekday 5 4.5 22.5 3 25.5
$
24.00
$
612.00
$
31,824
.00
Payable
for
Insuranc
e
$
9,100.
00
Payable
for
$
3,120.

2PROCESS AUTOMATION FOR UNIFORM ZOMBIE
Utility
cost 00
Payable
to
Replace
ment
worker 66
$
39.00
$
2,574.
00
$
10,296
.00
Total
Operatio
nal cost
$
1,09,0
96.00
From the, above table it is evident that the total operational cost is $109096. Which
includes the yearly payable of $10296 to the replacement worker.
Task 2
In order to find out the different insights from the past sales data the following charts
are created.
Number of Quotes
0 10 20 30 40 50 60 70
66
64
37
8
6
3
5
2
7
1
Distribution of Quote values
740-820 660-740 580-660 500-580 420-500
340-420 260-340 180-260 100-180 20-100

3PROCESS AUTOMATION FOR UNIFORM ZOMBIE
From the above chart, it is found that maximum number of uniforms are quoted for
$20-100 which sums up to 66 orders and the next price range with second highest number of
quotes is $100-180 is 64.
In the next chart, the manufacturing cost is plotted for different sizes of cloths. The
highest manufacturing cost is recorded for cloths with “M” size and summed up to $2234.9.
L
M
S
XL
XS
XXL
0 500 1000 1500 2000 2500
1686.2
2234.9
1321
2123.5
1715.9
1788
Sum of Manufacturing cost ($) by
Size
In the next chart the profit is plotted against the size of the cloths.
L
M
S
XL
XS
XXL
0 1000 2000 3000 4000 5000 6000 7000
4301.4
6175.7
3177.6
4080.7
4840
3924.3
Sum of Profi t by Size
Here, it can have stated from the above chart that, the maximum profit is recorded for
the “M” sized product and the profit is $6175.7. when compared with other sizes, it is found
that the second highest total profit ($4840) is recorded for XS size against the significantly
lesser amount of manufacturing cost which summed up to $1715.9.

4PROCESS AUTOMATION FOR UNIFORM ZOMBIE
In the next chart the profit share contribution of the employees are depicted.
Abbey and Constance
73%
Belle and Constance
27%
Sum of Profi t by Emploloyee
Abbey and Constance
Belle and Constance
From the above, chart it can be stated that maximum amount of profit is generated by
Abbey and Constance. Whereas Belle and Constance contributed only 23% of the total profit.
Task 3
As there are multiple size and material sizes that can be selected by the customer this
makes the quotation process complicated for the employees. Thus, in order to automate the
process, the accompanied excel sheet contains the following table from which the employees
will be able to select the material and size for the cloths and depending on the available
historical data, the last row generate a relevant quote value while including the enough profit
for the organization. The quote price is calculated using the “AVERAGEIFS” formula.
Select Material for
Clothing Tweed
Select Size of Clothing S
Estimated Quote 194

5PROCESS AUTOMATION FOR UNIFORM ZOMBIE
In the development of this table, it is found that there are certain combinations for
which there are no historical data is available thus for that a custom error message is
displayed using the “IFERROR” formula.
Task 4
As the organization manufactures the clothing according to the orders thus in order
make the business profitable even if the order is not accepted by the customer, the available
resources should be used in an optimized way. In this way, it will be possible to reduce the
wastage of resources.
Similar to the above table, in this task two new rows are added which will decide the
production decision. These are the “Popularity” and the “Stock Availability.” Following is
the capture of the created table.
Select Material for Clothing Tweed
Select Size of Clothing S
Estimated Quote 194
Select Popularity Popular
Select Inventory Status of Product In stock
Production Decision START PRODUCTION