ITECH1100: Excel Automation for Shirt Land Business Process Analysis

This project focuses on automating business processes for a company named Shirt Land, which specializes in manufacturing shirts. The project utilizes Microsoft Excel to calculate wages, fixed costs, and utility costs, and analyzes quote amounts and manufacturing costs through bar charts and p...Read More

Task 0:
The name of employee and their corresponding working hours in a week is represented in the
following table. The company named as Shirt Land is specialized in manufacturing shirts.
The work is divided among the three employees. Andrea and Bernita produces shirts while
Chris handles the sales and the delivery.
Name Specific Working days Working hours(per
week)
Additional Working
hours(per week)
Andre
a
Monday, Tuesday and
Wednesday
22.5 3
Bernit
a
Thursday and Friday 15 0
Chris Every weekday 22.5 3
Task 1:
Now, the wages and the fixed and utility cost in a year of the company is calculated by
suitable formula and with the provided information as given in the excel file. The table is
given below.

Name Specific
Working
days
Working
hours(pe
r week)
Additional
Working
hours(per
week)
Total
Working
hours (per
week)
Hourly
rates(i
n $)
Annua
l
cost(in
$)
Andrea Monday,
Tuesday and
Wednesday
22.5 3 25.5 26 34476
Bernita Thursday and
Friday
15 0 15 26 20280
Chris Every
weekday
22.5 3 25.5 24 31824
The
replacement
employee
66 39 10296
Yearly
Fixed
cost(in $)
9100
Annual
Utility
cost(in $)
3120
Annual
Total
Outgoing
cost(in $)
109096

So, the total outgoing cost of Shirt Land is $109096.
Task 2:
The different quote amount and manufacturing cost of the shirt in different date and time is
represented by the following bar charts.

2018-06-04
2018-06-11
2018-06-15
2018-06-21
2018-06-26
2018-07-03
2018-07-04
2018-07-11
2018-07-17
2018-07-23
2018-07-26
2018-08-01
2018-08-07
2018-08-10
2018-08-16
2018-08-22
2018-08-27
2018-08-30
2018-09-03
2018-09-05
2018-09-07
2018-09-12
2018-09-14
2018-09-18
2018-09-21
2018-09-25
2018-09-28
2018-10-02
2018-10-04
0 20 40 60 80 100 120 140 160 180
Manufacturing cost ($)
Now, the quote amount variation of each unique individual category from staff to staff is
shown by pivoting and then forming a column chart where average, sum and standard
deviation of each category of shirt is displayed as given below.

Now, in above column chart it is shown that the variation of quote amount of SilkXXL shirts
is largest as it has the highest standard deviation and the most popular category of shirt is
SilkXL i.e. the shirts made from silk and of XL size is sold the most.
Task 3:
Now, based on the swim lane diagram shown below for maintaining same quote price for
each unique category and to reduce operating time an automated excel dropdown list is
created where staffs can choose the size and material of garments as ordered by the customer
and the quote amount will be automatically displayed. The quote amount for each unique
category is the average quote amount of that category in the historical years.

Demo run:
Display to staff members
Material Size Quote Amount(in $)
Wool XXL 196.8333333
Staff members Usage
Task 4:
Now, the above process can be improved in which the popular and not popular items as
ordered by customers can be automatically detected based on a predefined level (here in the
historical year the items sold to number of customers equals or more than 10 are considered
popular). Now, the company inventory is unknown and hence this is randomized in excel by
in stock and not in stock status and based on that item manufacture is started.

Now, the process improvement of the task can be improved where the popular and unpopular
unique category is automatically detected based on some specified limit decided by the staff
members and the quote amount will be shown to the staff. Now, if customer accepts the quote
then the inventory is checked if it is in stock then the shirt is delivered otherwise it is
displayed to start manufacture. Now, here as popular or non-popular items can both, either or
none can or cannot be in the inventory hence it is uncertain or stochastic process. Hence, the
inventory status is randomized with choosing between in stock and out of stock option in
excel.
Sample run:
Material Size Quote Acceptance by Cutomer
Wool XXL Not Accepted
Popularity Quote Amount(in $) Stock Status Manufacture Item
Popular 196.8333333 Out of Stock Do not Start Manufacture
Staff members Usage
Automatic Display to Staff Members