ITECH1100 Assignment 1: Analyzing and Automating Suit Box Processes

This assignment focuses on automating the business processes of Suit Box, a small clothing manufacturer, using Microsoft Excel. The solution begins with analyzing employee work hours and calculating operational costs, including wages, insurance, and utilities. It then delves into data analysis, visualizing sales trends by comparing order frequency with price ranges, manufacturing costs, and employee profit generation. The core of the assignment involves automating the quotation process, enabling the system to provide minimum quote prices based on customer-selected suit sizes and materials. Finally, the assignment extends this automation to include production decisions, considering product popularity and inventory availability. The student utilizes Excel functions to create dynamic tables and automate decision-making, demonstrating an understanding of business process automation and data analysis techniques. The assignment covers data analysis, visualization, quotation, and production automation.

Running head: AUTOMATION OF BUSINESS PROCESSES FOR SUIT BOX
Automation of business processes for Suit Box
Name of the Student
Name of the University
Authors note

1AUTOMATION OF BUSINESS PROCESSES FOR SUIT BOX
Task 0
According to the given information provided the business organization Suite Box runs
its business depending on the three employees which are Agnes, Bhavesh and Celeste. Out of
this three employees of the organization Agnes and Bhavesh are skilled manufacturing staffs
thus they manage the manufacturing of the suits and Celeste manages the ordering process as
well as the delivery of the products to the customers. According to the given data, the
following table is developed that shows the shift hours (day wise and weekly).
SuitBox
Name
Day
Count
Shift
hours
Overtime for Social
Presence
Shift Hour every
Week
Agnes 3 7.5 3 25.5
0
Bhaves
h 2 7.5 15
0
Celeste 6 4.5 3 30
Task 1
In this part, the operational cost for running the business is calculated. In order to do
this, the annual payment to the employees, cost required for the replacement worker (for four
weeks), insurance cost as well as utility cost if considered. The table below shows the
complete calculation for determining the total operational cost.
SuitBox
Name Day Count Shift hours
Overtime for
Social Presence
Shift Hour
every Week
Agnes 3 7.5 3 25.5
0
Bhavesh 2 7.5 15
0
Celeste 6 4.5 3 30
0

2AUTOMATION OF BUSINESS PROCESSES FOR SUIT BOX
Replacement Worker
for Four Weeks 6 70.5
Name Pay/Hour
Wage
Paid/Week Wage Paid/Year
Agnes
$
26.00
$
663.00
$
34,476.00
$
-
$
-
Bhavesh
$
26.00
$
390.00
$
20,280.00
$
-
$
-
Celeste
$
24.00
$
720.00
$
37,440.00
Total Wage
$
92,196.00
Other Costs
Insurance Cost
$
9,100.00
Utility cost($
260/month)
$
3,120.00
Total
$
12,220.00
Replacement
Worker 10998
Total Operational Cost
$
1,15,414.00
After considering the all the variables required for the operational cost, the annual
cost is summed up to $ 115414.
Task 2
From the previous sales data certain insights are explored which are depicted below;
For the first graph the frequency of ordered suits are compared with the price ranges.

3AUTOMATION OF BUSINESS PROCESSES FOR SUIT BOX
30-100 100-170 170-240 240-310 310-380 380-450 450-520 520-590 590-660 660-730
69 73
47
19
4 3 1 2 1 1
Count of Quotes
From the above graph, it is clearly visible that maximum number of orders (73) are
placed having their quote price $100-170.
In the next graph, the trend of the manufacturing cost and the quote amount for the
products are compared.
2018-06-04 2018-06-19 2018-07-04 2018-07-16 2018-08-01 2018-08-14 2018-08-27 2018-09-06 2018-09-19 2018-09-28
0
100
200
300
400
500
600
700
800
Comparison of the Quote and Manufact
Quote amount Manufacturing cost ($)
Observing the above graph, it can be stated that after 2018-08-02 the difference
between the quote amount and manufacturing cost has been increased thus it can be said that,
the profit after this time has been increased. In addition to that, the number quote beyond the
value of 400 also has been increased after the aforementioned date.
In the last graph, the comparison of profit for different employees is compared.

4AUTOMATION OF BUSINESS PROCESSES FOR SUIT BOX
15851.1; 68%
7557.8; 32%
Sum of Profit by Employees
Agnes & Celeste
Bhavesh & Celeste
From, the above graph can be stated that, maximum percent ( 62%) of profit is
generated by Agnes and Celeste and remaining 32% is generated by Bhavesh and Celeste.
Task 3
In this section the quotation process is automated using the built in excel functions.
As there are multiple combinations available for the customers that may include one of the
available 6 sizes (XL, XS, XXL, S, L, M) of the suits and one of the five available materials.
Depending on the available previous sales data within the built-in functions the
following table is created where the employee can select the size and the material mentioned
by the customer in order to get the minimum quote price for the specific order.
SELECT MATERIAL FOR
SUITE Silk
SELECT SIZE OF SUITE XXL
ESTIMATED QUOTE 337

5AUTOMATION OF BUSINESS PROCESSES FOR SUIT BOX
Task 4
In this section, the previously created table is extended in order to automate the
production decision of the suits. Depending on the popularity and availability of the product
in the inventory the production decision is automated.
SELECT MATERIAL FOR
SUITE Silk
SELECT SIZE OF SUITE XXL
ESTIMATED QUOTE 337
Availability Not in Inventory
Popularity of the Product Un-Popular Suite
Production Decision Do Not Start