ITECH1100 Assignment 1: Shirt City Digital Revolution Analysis

This assignment focuses on analyzing Shirt City's business operations using Microsoft Excel. The student analyzes employee costs, utility and insurance expenses to determine the total operational cost. The student then analyzes sales data, creating charts to visualize quote value distribution and weekday sales trends using the TEXT function. Furthermore, the assignment involves automating the quoting process by using the AVERAGEIFS function to suggest quote prices based on historical sales data for different shirt sizes and materials. Finally, the student automates production decisions by integrating material, size, popularity, and inventory status to determine whether to start production. The assignment demonstrates the application of Excel functions for data analysis, visualization, and automation of business processes, offering insights into the impact of digital tools on a small business.

Running head: UNDERSTANDING THE DIGITAL REVOLUTION: SHIRT CITY
Understanding the Digital Revolution: Shirt City
Name of the Student
Name of the University
Authors note

1UNDERSTANDING THE DIGITAL REVOLUTION: SHIRT CITY
Task 0
Shirt City which is the manufacturer of shirts and specializes in designing custom
made and one-of-a-kind shirts sells their products against the requests through their online
store. Three employees of which Celestine deals with the orders, sales and delivery. On the
other hand, other two employees Amy and Bhavneet work together in the manufacture
section. In the event that the client affirms their request, Amy and Bhavneet fabricate the
arranged items, and Celestine sorts out the delivery of the projects.
Task 1
In the following section the cost for the three employees along with the replacement
worker for four days is also considered while calculating the totals operational cost for Shirt
City. Along this lines the annual operating cost for the business also includes the Annual
utility cost of $ 3,120 and the insurance cost of $9100. According to the provided
information Amy works (3*7.5) 22.5 hours in a week (working on Mondays, Tuesdays and
Wednesdays), Bhavneet works 15 hours in a week(works on Thursdays and Fridays) and
Celestine works 22.5 hours every week.
Name
of the
Staff
Days’
work on
Week
ly
work
ing
days
Worki
ng
time
(in
hours)
Total
workin
g
hours
weekly
Extra
Hours
working
every
week
Total
Workin
g hours
every
week
Hourly
Rate
w
ee
kl
y
p
a
y
a
bl
e
Ye
arl
y
pa
yb
le
Amy Monday,
Tuesday
and
Wednesd
ay
3 7.5 22.5 3 25.5 $
26.00
$
6
6
3.
0
$
34,
47
6.0
0

2UNDERSTANDING THE DIGITAL REVOLUTION: SHIRT CITY
0
Bhavn
eet
Thursda
y, Friday
and
Saturday 3 7.5 22.5 0 22.5
$
26.00
$
5
8
5.
0
0
$
30,
42
0.0
0
Celesti
ne
Monday
to
Friday 5 4.5 22.5 3 25.5
$
24.00
$
6
1
2.
0
0
$
31,
82
4.0
0
Replac
ement
worker 73.5
$
39.00
$
2,
8
6
6.
5
0
$
11,
46
6.0
0
Fixed
Costs
Insura
nce
Cost
for
Busine
ss
$
9,1
00.
00
Utility
Cost
(Every
Month
)
$
260.00
$
3,1
20.
00
Total
Operat
ing
Cost
$
1,2
0,4
06.
00
Task 2
From the given excel sheet the frequency of the quote values among different ranges
are depicted in the following chart.

3UNDERSTANDING THE DIGITAL REVOLUTION: SHIRT CITY
Frequency
0 10 20 30 40 50 60 70 80 90 100
Distribution of quote
720-800
640-720
560-640
480-560
400-480
320-400
240-320
160-240
80-160
0-80
count of quote
Value Range
From the above chart, it can be easily stated that most of the orders are placed with
the quote range of $80-160 which is total 87 orders. The next range is 160-240 in which only
56 orders are placed. Minimum number of orders are placed for the quote range 720-800
price range (only one order).
Mon Tue Wed Thu Fri
0
5
10
15
20
25
30
35
40
45
50
42 40
47
39 45
Distributions of quotes on Weekdays
Axis Title
Axis Title
From the given dates, through the use of the “TEXT ()” Formula the day’s columns
are generated and day wise quotes are compared as depicted in the above chart. The TEXT
function lets you change the way a number appears by applying formatting to it with format

4UNDERSTANDING THE DIGITAL REVOLUTION: SHIRT CITY
codes. The “TEXT ()” function is considered as useful in the scenarios where it is required to
display the given numbers in different format. This function is also useful in combining
numbers with different text/symbols.
From the chart it can be easily said that maximum orders are booked on Wednesday.
The second highest day with quote values is Friday.The following is the profit compared to
the different days. The highest share of profit is generating on the Wednesday. On the
contrary to the quote trend, the second highest profit was recorded on Tuesday. The lowest
share of profit is recorded on the Monday and Friday.
Mon
22%
Tue
24%
Wed
26%
Thu
14%
Fri
14%
Sum of profit by Weekday
Mon
Tue
Wed
Thu
Fri
Task 3
The following table is created in the accompanied excel sheet in which through the
use of the Data validation and “Define Name” option the options are generated for the
employees of the Shirt city. As shown in the below table, the employees will be able to select
the size and the material according the requests of the customers. In order to automate the
quoting process, the “AVERAGEIFS” Function is utilized in order to match with the multiple
conditions and based on the historic recorded sales value the employees can easily get the
profitable quote price.

5UNDERSTANDING THE DIGITAL REVOLUTION: SHIRT CITY
The “AVERAGEIFS” function is used in order to automate the process of quoting as
it will enable the employees of the organization generate a quote value that are relevant with
the previous orders with the similar size and material for the hat.
Select Size of the Product S
Select Material for the Product Wool
Suggested Quote Price 139
Task 4
In order to automate the production decision depending on the availability and
popularity of the shirts, the following table is created in the excel file.
Select Material for the Product Wool
Select Size of the Product L
Suggested Quote Price 156
Select Popularity Popular
Select Inventory Status
Not
Available
Production Decision Start
Here. Along with the previous options the two new options are added and the
employees will be able select the options right beside the gold coloured columns.