Hat Bonanza Case Study: Excel Automation and Data Analysis Project

This assignment solution focuses on automating and analyzing the business processes of "Hat Bonanza" using Microsoft Excel. The solution calculates the total operational costs by considering employee salaries, replacement worker pay, and utility costs, presenting the data in a clear table. It also includes data visualization through charts that depict the distribution of quotes across different price ranges and the average profit for various hat materials. Furthermore, the assignment automates the quotation process by using the "AVERAGEIFS" function to generate quote values based on selected hat sizes and materials. Finally, it automates the production decision-making process using "nested if" statements to determine whether to start production based on the hat's popularity and availability.

ITECH1100 Understanding the Digital Revolution: Hat Bonanza
Name of the student
Name of the University
Authors note

Task 0
Hat Bonanza gets orders through their online store and the business is managed by the
three employees namely Abraham, Bhaljeet, and Cliff. Cliff deals with the orders of the
products, sales and delivery. On the other hand, Abraham and Bhaljeet together manufactures
the ordered hats. For any order of a hat when the client affirms their request for the hat,
Abraham and Bhaljeet manufacture the arranged items, and Cliff sorts out the delivery of the
product.
Task 1
In order to calculate the total operational cost, the payable to the three employees,
payable to the replacement worker and the insurance as well as utility cost needs to be
considered. As the utility cost provided for every month thus we calculated the annual cost
for the same.
Following table includes all the required values and is used to calculate the total
annual operational cost.
Name
of the
Staff
Days
work on
Week
ly
worki
ng
days
Worki
ng
time
(in
hours)
Total
workin
g hours
weekly
Extra
Hours
working
every
week
Total
Working
hours
every
week
Cos
t/h
our
w
ee
kl
y
p
a
ye
bl
e
ye
arl
y
pa
ya
ble
Abrah
am
Monday,
Tuesday
and
Wednesd
ay 3 7.5 22.5 3 25.5
$
26.
00
$
6
6
3.
0
0
$
34,
47
6.0
0
Bhaljee
t
Thursday
, Friday
2 7.5 15 0 15 $
26.
$
3
$
20,

00
9
0.
0
0
28
0.0
0
Cliff
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 60 6 66
$
39.
00
$
2,
5
7
4.
0
0
$
10,
29
6.0
0
Insura
nce
and
rent
cost
$
9,1
00.
00
Utility
cost
(month
ly) 260
$
3,1
20.
00
Total
yearly
operati
ng cost
$
1,0
9,0
96.
00
From the above table it can be stated that, including all the required costs the total
operational cost for the “Hat Bonanza” is given by $ 1,09,096.
Task 2
In order to have better insights from the available past sales data following two charts
are created. For the first chart, the number of quotes in the different price rages are depicted.

0-20 20-70 70-
120
120-
170
170-
220
220-
270
270-
320
320-
370
370-
420
420-
470
470-
520
520-
570
570-
620
620-
670
670-
720
720-
770
770-
820
0
10
20
30
40
50
60
70
80
0
9
68
37
30
23
9 3 5 0 4 4 2 0 0 1 2
Distribution of quotes in diff rent price range
From the above chart, it can be stated that most number of the hats are quoted with the
price range of 70-120 which counted up to 68 hats. Again for the price range 620-720 there
are no hats ordered by the customers. Again in order to find out the day of sales the “TEXT”
formula is utilized which automatically generates the days depending on the provided date.
In the next chart, the average profit is compared to different materials are calculated
are depicted in the chart.
Cotton
Leather
Silk
Tweed
Wool
0 50 100 150 200 250 300 350
Average of Profi t by Material
Total
Here, it can be stated from the above diagram, that for the Silk the average profit is
maximum. The second highest average profit is for Tweed.

Task 3
In order to automate the quotation process of the hats at Hat Bonanza, the excel sheet
contains the automation table that generates a quote value depending on the selected size and
the material of the hat. The quote price is generated depending in the past historic value.
Following is the table that contains the options that have the options to select the
material and the size. The list of size and materials are used through the “data validation”
and “Define name” functions are utilized.
SELECT MATERIAL FOR
HAT Wool
SELECT SIZE FOR HAT XXL
QUOTE PRICE 168
In order to get the quote values, the “AVERAGEIFS” function is used depending on
the past quotes of the selected combination.
Task 4
For this part, the production decision making process is automated through the excel
formulas and techniques. For the popular, non-popular, available and non-available hats are
considered. Through, the use of the “nested if” this automation is achieved.

SELECT MATERIAL FOR
HAT Wool
SELECT SIZE FOR HAT M
QUOTE PRICE 139
SELECT POPULARITY Popular Hat
SELECT AVAILABILITY Unavailable
PRODUCTION DECISION
START
PRODUCTION