Excel Automation for Garment Oasis: ITECH1100 Project, Semester 1

This project provides an automated solution for the Garment Oasis, a small clothing manufacturer, using Microsoft Excel. It details employee wage calculations, including annual costs and utility expenses, to determine the total outgoing cost. The project analyzes quote amount variations over time and across different staff members using pivot tables and column charts. A swim lane diagram is developed to improve the quote process, leading to an automated system that provides quote prices based on material and size. Furthermore, the system identifies popular and unpopular items, randomizes inventory status, and initiates manufacturing accordingly. This comprehensive approach demonstrates the automation of key business processes within the Garment Oasis.

Task 0:
The automated solution for the Garment Oasis is produced in this assignment by using excel.
The Garment Oasis is basically a small size clothing manufacturer that produces user
specified and one-of a kind garments and sell the products through online store. There are
total 3 staff members namely Arthur, Britney, and Cheryl working in different shift timings
and different days. Arthur and Britney produces garments. Orders, sales and delivery are
managed by Cheryl.
The employee name and their total working hours in a week are represented in the following
table.
Name Specific Working days Working hours(per
week)
Additional Working
hours(per week)
Arthur Monday, Tuesday and
Wednesday
22.5 3
Britne
y
Thursday and Friday 15 0
Cheryl Every weekday 22.5 3
Task 1:
The wages for the employees (including the replacement employee) in a year is calculated
using excel for respective staff members’ as given below. In addition the yearly utility cost
and the fixed cost is combined with the yearly wage cost for calculation of the Annual
outgoing cost using excel.

Name Specific
Working days
Working
hours(per
week)
Additional
Working
hours(per
week)
Total
Working
hours (per
week)
Hourly
rates(in
$)
Annual
cost(in
$)
Arthur Monday,
Tuesday and
Wednesday
22.5 3 25.5 26 34476
Britney Thursday and
Friday
15 0 15 26 20280
Cheryl Every
weekday
22.5 3 25.5 24 31824
employee as
a
replacement
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 the Garment Oasis is $109096.
Task 2:
The variation of quote amount with respect to time for all materials and sizes is given below
by the following line chart.
The manufacturing cost variation over time is shown below by the following line chart.

04-06-201818-06-201803-07-201816-07-201827-07-201808-08-201820-08-201829-08-201812-09-201825-09-201805-10-2018
0
20
40
60
80
100
120
140
160
Manufacturing cost ($)
Now, for showing the quote amount variation for three different staff members the average,
standard deviation and number of each unique category of material and size combination is
produced by excel pivoting through a column chart as shown below. In the pivot table the
variation of the

From the above column chart it is clear that the average and the standard deviation of
SilkXXL category or cloths made using silk and are of XXL size has varied maximum in
quote amount from staff to staff. While, the least variation excluding the categories which are
sold only once in historical year is for CottonXXL.
Task 3:
Now, for the process improvement and maintaining equal quote price for each unique
category a swim lane diagram is developed with the help of staff members as given below.
Based on that swim lane an automated system is developed in excel in which by choosing the
material and size of garment as asked by customer the quote price will be displayed to the
staff member which is the average quote price of that category obtained from historical data.

Sample run:
Display to staff members
Material Size Quote Amount(in $)
Wool XL 165.6125
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.
Sample run:
Material Size Quote Acceptance
Cotton L Accepted
Popularity Quote Amount(in $) Stock Status Manufacture Item
Popular 79.68461538 Not in Stock Start Manufacture
Staff members Usage
Automatic Display to Staff Members