ITECH1100 Digital Revolution: Automating Hat Frenzy Business Process

This assignment solution for ITECH1100 'Understanding the Digital Revolution' focuses on automating the 'Hat Frenzy' business process using Microsoft Excel. It includes tasks such as setting up an ePortfolio page, calculating the cost of operating the business with employee working hours and rates, visualizing order information using 2D excel graphs and pivot charts to analyze quotation variations and manufacturing costs, managing price consistency through Excel formulas for material and size selection, and improving process involvement by introducing automated functions for stock identification and manufacturing initiation. The solution demonstrates the application of Excel skills for data visualization, analysis, and business process automation, providing a practical approach to solving a business problem.

Running head: ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
ITECH1100 Understanding the Digital Revolution
Assignment 1 – Hat Frenzy (30356405)
Name of the Student
Name of the University
Author’s Note

1
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
Table of Contents
Task 0 – Setting Up....................................................................................................................2
Task 1 – Cost of Operating the Business...................................................................................2
Task 2 – Order Information........................................................................................................3
Task 3 – Price Consistency........................................................................................................5
Task 4 – Process Involvement....................................................................................................6
Bibliography...............................................................................................................................8

2
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
Task 0 – Setting Up
E Portfolio Page
Name Working days Working
hours(in week)
Additional Working
hours( in week)
Augusta Monday, Tuesday and
Wednesday
22.5 3
Brutus Thursday and Friday 15 0
Conrad Every weekday 22.5 3
Task 1 – Cost of Operating the Business
Name Working
days
Working
hours(in
week)
Additional
Working
hours( in
week)
Total
Working
hours (in
week)
Hourly
rates(i
n $)
Annua
l
cost(in
$)
Augusta Monday,
Tuesday
and
Wednesday
22.5 3 25.5 26 34476
Brutus Thursday
and Friday
15 0 15 26 20280
Conrad Every
weekday
22.5 3 25.5 24 31824
Replacement
employee
66 39 10296
Annual Fixed
cost(in $)
9100
Annual Utility
cost(in $)
3120
Annual Total 109096

3
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
Outgoing
cost(in $)
The above table explains the number of annual cost for the three employees
considering the working days and hours in the week being done by the employees. Total
working hours for the three employees have also been calculated through adding the working
hours in a week and additional working days. This value can further be used for calculating
the annual cost through multiplying the rate per hour and total working hours in the whole
year. Formulas have been used in the operating_cost.xls file for easy calculation that will
automatically calculated the annual cost of the employees throughout the year.
Task 2 – Order Information
The two dimensional excel graph is prepared using the information provided in the
requirements as demonstrated below:
Quotation of variation on time
2018-06-04 2018-06-18 2018-06-29 2018-07-12 2018-07-27 2018-08-08 2018-08-16 2018-08-27 2018-09-05 2018-09-14 2018-09-21 2018-10-01
0
100
200
300
400
500
600
700
800
900
Quote amount
The above representation demonstrates the graphical representation of the quotation
of variation on time that shows high peak up to 680 and lowest up to 25. The above graphical
representation can be used for comparing the quotation amount with respect to the dates. The

4
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
maximum quote was made in between March and May 2018. The historical data and the
present data can be visualized for predicting the probability of the future quotations based on
the practices being made in the past.
2 0 18 - 0 6 - 04 2 0 18 - 0 6 - 20 2 0 18 - 0 7 - 06 2 0 18 - 0 7 - 20 2 0 18 - 0 8 - 07 2 0 18 - 0 8 - 16 2 0 18 - 0 8 - 29 2 0 18 - 0 9 - 10 2 0 18 - 0 9 - 20 2 0 18 - 0 9 - 28
0
20
40
60
80
100
120
140
160
Manufacturing cost ($)
The identical combinations of the different categories unique combination of the date and the
manufacturing cost. For the overall representation of the quote amount and the materials used
can be represented as the calculations of the following standard deviation, mean value, and
the count of the quote amount. The following graph represents the bar values for all the
quotations and the materials used in the product.

5
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
CottonL
CottonS
CottonXS
LeatherL
LeatherS
LeatherXS
SilkM
SilkXL
TweedL
TweedS
TweedXS
WoolL
WoolS
WoolXS
(blank)
0
100
200
300
400
500
600
Count of Quote amount
StdDev of Quote amount2
Average of Quote amount2
The above pivot chart represents all the values and the graphical representation of all
the values of the different calculations as mentioned above and the above graph represents the
three calculations based on the materials used. It is a combination of the data representation
of the different types of materials used, standard deviation, count of quote amount, and the
average of quote amount2. The Silk XL has the maximum average of quote amount and
maximum standard deviation quote amount in addition to the maximum count of quote
amount for the leather XS.
Task 3 – Price Consistency
The price consistency can be managed through the easy application of the Excel in
which simple formulas can be used for the utilization of the drop down technique where the
different types of the materials can be selected. It will also allow the selection of the different
sizes those could be selected along with the material type and thus, prediction and analysis of
the quote amount can be identified automatically. The employees would be allowed the easily

6
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
and effectively predict the quotation of the quote amount based on the previous predictions
being made in the historical data. The different formulas can be used for the development of
the spreadsheet that can allow the efficient and effective management of the related
operation.
Task 4 – Process Involvement
The improvement and modifications can be made in the system for enhancing the
functionalities and operations of the excel sheet. The automations can be introduced using the
excel formulas. The following picture demonstrates the automated functions in the excel
sheet and would allow the users to identify which stocks are required and whose
manufacturing needs to be initiated for further production. The output of the developed
spreadsheet can be demonstrated in the following screenshot:

7
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
The new sections have been introduced for facilitating more options that will be best
suiting the users. The automated data and notification will be generated based on the
demands and the sales being made in the past.

8
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
Bibliography
da Silva, R. J., Pennecchi, F. R., Hibbert, D. B., & Kuselman, I. (2018). Tutorial and
spreadsheets for Bayesian evaluation of risks of false decisions on conformity of a
multicomponent material or object due to measurement uncertainty. Chemometrics
and Intelligent Laboratory Systems, 182, 109-116.
Gu, W., & Li, S. (2017). EXCEL Advanced Tips and Tricks: Filter, Data Sort, Pivot Table,
and Graphics.
Homocianu, D. (2015). Excel Power Pivofs Applications in Audit and Financial
Reports. Audit Financiar, 13(131).
Qwaider, S. R., & Abu-Naser, S. S. (2018). Excel Intelligent Tutoring System.