ITEC1100 Assignment 1: Automating Business Processes for Hat Frenzy

This assignment solution for ITEC1100's "Understanding the Digital Revolution" focuses on automating business processes for a fictional company called "Hat Frenzy" using Microsoft Excel. The project involves multiple tasks, starting with setting up an ePortfolio page and calculating the cost of operating the business by considering employee working hours, rates, and other expenses. The solution then visualizes order information using graphs, analyzes price consistency through the use of drop-down menus and formulas, and suggests process improvements through automation. The student utilizes Excel features to create charts, pivot tables, and formulas to analyze data, providing insights into costs, quotations, and inventory management. The final section suggests improvements to the system by introducing new tabs that incorporate demand, sales, and stock status to aid management in decision-making. The assignment demonstrates an understanding of data visualization, automation, and basic business principles within the context of a digital business environment. A bibliography is also included at the end of the assignment.

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....................................................................................................5
Bibliography...............................................................................................................................7

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)
Total Working
hours (in week)
Alyx Monday, Tuesday and
Wednesday 22.5 3 25.5
Blanch
e Thursday and Friday 15 0 15
Carrie Every weekday 22.5 3 25.5
Task 1 – Cost of Operating the Business
Name Working days Working
hours(in
week)
Additional
Working
hours(in week)
Total
Working
hours (in
week)
Rate per
hour ($)
Annual
Cost
($)
Alyx Monday,
Tuesday and
Wednesday
22.5 3 25.5 26 34476
Blanche Thursday and
Friday
15 0 15 26 20280
Carrie Every weekday 22.5 3 25.5 24 31824
Replacement 66 39 10296
Annual Fixed cost(in $) 9100
Annual Utility cost(in $) 3120
Annual Total Outgoing cost(in $) 109096
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.

3
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
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-042018-06-20 2018-07-03 2018-07-16 2018-07-242018-08-06 2018-08-10 2018-08-202018-08-30 2018-09-07 2018-09-18 2018-09-27 2018-10-04
0
100
200
300
400
500
600
700
800
Quote of Variation on time
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. It visualises the data
of the quotations made between the June and October 2018. It can be used for analysing the
history of the charts and predicting the strategies for future strategies.

4
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
2018-06-04 2018-06-21 2018-07-04 2018-07-16 2018-07-26 2018-08-07 2018-08-14 2018-08-23 2018-09-03 2018-09-13 2018-09-24 2018-10-02
0
20
40
60
80
100
120
140
160
Manufacturing cost ($)
The above graphical represents the unique combinations different categories of the materials
considering the difference between quote amount for each staff members. The overall
calculation and graphical values can be represented as the average and standard deviation in
the following graph:
CottonL
CottonS
CottonXS
LeatherL
LeatherS
LeatherXS
SilkL
SilkS
SilkXS
TweedL
TweedXL
WoolM
WoolXL
WoolXXL
0
500
1000
1500
2000
2500
3000
3500
4000
Sum of Quote amount
Average of Quote amount2
StdDev of Quote amount2

5
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
Pivot table is being used for the representation of the above graph that demonstrates
the calculation of all the combination of the collected data including (sum of quote amount,
average of quote amount2, and standard deviation of quote amount2). The above graph
explains that the material type Silk S of the hats is having the maximum variation considering
the quote given by all the three employees or the staff members. Silk M has also the
maximum average quote amount that tends to 500 in addition to the maximum standard
deviation quote amount for the silk L.
Task 3 – Price Consistency
The excel sheet is being prepared including the drop downs with the list of the
material types that can be used by the staffs in addition to the size for the identification of the
quote amount. In manner to manage the consistency in pricing of the every category of items
produced in the Hat Frenzy. It will allow an ease to the employees to make the exact
quotation of the material without the chaos of looking into the charts based on the
consideration of the history wages of the hat frenzy. Excel formulas have been used to make
sure that the input data is easy and the calculation can be made in much easier and efficient
way as demonstrated below:
Task 4 – Process Involvement
The system require some improvement and hence, automation can be developed
through using the excel formulas that can inform the management about the needs and

6
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
requirements of the new product based on the demand. The following figure demonstrates the
output of the prepared excel sheet for this purpose:
Excel is being used for the introduction of the new tabs including the popularity,
material, size, Quote acceptance, Quote amount, Stock Status and Manufacture Item. The
data will be calculated using the available demands and sales being made from the stores or
the inventory. ‘Start manufacture’ output will appear if the quote is accepted and the item is
not available in the basement and so on.

7
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
Bibliography
Merlo, J., Wagner, P., Austin, P. C., Subramanian, S. V., & Leckie, G. (2018). General and
specific contextual effects in multilevel regression analyses and their paradoxical
relationship: A conceptual tutorial. SSM-population health, 5, 33-37.
Richards, G., & Grinsted, S. (2016). The Logistics and Supply Chain Toolkit: Over 100 Tools
and Guides for Supply Chain, Transport, Warehousing and Inventory Management.
Kogan Page Publishers.
Rubin, S. J., & Abrams, B. (2015). Teaching fundamental skills in Microsoft Excel to first-
year students in quantitative analysis. Journal of Chemical Education, 92(11), 1840-
1845.
Sharma, S., & Malhotra, A. (2015). Safety stock calculations and inventory analysis: a
practical approach for the FMCG case in a South-East Asian country. International
Journal of Advanced Logistics, 4(3), 131-144.