ITECH1100: Digital Revolution Assignment 1 - Shirt Bonanza

This assignment solution for ITECH1100, titled 'Shirt Bonanza,' demonstrates the application of data analysis and automation techniques using Microsoft Excel. The solution covers various tasks, including calculating the cost of operating the business by analyzing employee working hours and wages, and visualizing order information through graphs to show quote amount variations over time. It further explores price consistency by developing an automated system for staff quoting, and integrates features for inventory management based on collected data. The solution utilizes Excel's features such as pivot tables, dropdown menus, and formulas to provide a comprehensive analysis of the provided dataset and automate business processes. The assignment showcases the ability to interpret data, create visualizations, and provide automated solutions using Excel.

Running head: ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
ITECH1100 Understanding the Digital Revolution
Assignment 1 – Shirt Bonanza (30359428)
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
This task was all about the calculation of the working hours for the three staff
members Amanda, Barry, and Cassandra. The working hours in week is calculated through
multiplying the working days with the hours they serve to the organization. Moreover, two of
the team members as mentioned in the following table also serves additional working hours.
Name Working days Working hours(in
week)
Additional Working
hours( in week)
Amanda
Monday, Tuesday and
Wednesday 22.5 3
Barry Thursday and Friday 15 0
Cassandra Every weekday 22.5 3
Task 1 – Cost of Operating the Business
Annual cost
Employees TYPE
Cost/
hour
Working
Days
working
hour/week
Extra
hour/week
Total working
hour/week
Annual
cost
Augusta skilled 26 3 22.5 3 25.5 34476
Bhavesh skilled 26 2 15 0 15 20280
Celia
Part
Time 24 2 22.5 3 25.5 31824
Replacement 39 66 10296
Employee
wages 96876
Fixed Cost 9100
Utilities costs 3120
Total Annual
Cost($) 109096
The data collected in task 0 is used for calculating the overall annual cost being
selected for determining the operating cost of the organization. The elements including
working days, working hours and cost per hour is multiplied for the calculation of the annual
cost. Thereafter, fixed cost and overtime also have been added for the total annual cost.

3
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
General excel adding and multiplication formula is used for the overall calculation as it is
demonstrated in the excel sheet. Outcome is that the total annual cost for operating and
paying the staffs is $ 109096 including the wages of the employees for one year.
Task 2 – Order Information
The received dataset has the information including date of the quotes and quote
amount, which is used for projecting the following graph. The prepared graph shows the
variation in the quotes depending upon the time interval of June to October 2018. The
following graph shows the highest peak was during September and October when the value
reaches nearly to 900.
2018-06-04 2018-06-18 2018-07-02 2018-07-12 2018-07-20 2018-07-31 2018-08-13 2018-08-23 2018-09-03 2018-09-12 2018-09-20 2018-10-02
0
100
200
300
400
500
600
700
800
900
Quote amount
Figure 1: Quote amount v/s Time Variation
Thereafter, the received dataset had the time variations and the manufacturing cost
variations in those timeline. This data is being used for forecasting the rest of the information
and is being demonstrated in the following graph:
Again, the highest peak can be seen in the end of the month September where the cost
crosses $ 160. Thereafter, for the results, the data of size and materials have been

4
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
concatenated for assuring that each data will have the unique ids and further, it can be used
for forecasting the count, average and standard deviation of all the collected data.
2018-06-04 2018-06-18 2018-07-02 2018-07-12 2018-07-20 2018-07-31 2018-08-13 2018-08-23 2018-09-03 2018-09-12 2018-09-20 2018-10-02
0
20
40
60
80
100
120
140
160
180
Manufacturing cost ($)
The following graph is prepared using the PIVOT tool of Excel that helped in
forecasting the values related to the count of the quote, standard deviation, average of the
quoted amount with respect to the concatenated value of the material and size.
CottonL
CottonS
CottonXS
LeatherL
LeatherS
LeatherXS
SilkL
SilkS
SilkXS
TweedL
TweedS
TweedXS
WoolM
WoolXL
WoolXXL
0
100
200
300
400
500
600
700
Count of Quote amount2
StdDevp of Quote amount2
Average of Quote amount2

5
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
The most peak value crosses 600 that is the product made of Tweed and of Size large.
Furthermore, product made of cotton and size small that shows the maximum variation as per
the graph and similar projection can be seen with the product made of Wool of size XL.
Moreover, the average values have been also projected in addition to the standard variation
results. The maximum value reaches up to 200 as per the graphical representation.
Task 3 – Price Consistency
The data collected in the task 2 can be further used for developing an automated
system that could be used by the staff members for making a quote that is profitable and
genuine for the staff members. The dropdown and the formulas is being demonstrated in the
excel sheet provided where the dropdown menu is also introduced for allowing the flexibility
for the staff members.
For Staff members
Material Size
Cotton XS For Staff Members
Quote Amount(in $)
66.44
Task 4 – Process Involvement
Based on the collected and projected data in the above tasks, additional features are
also being introduced that will further automate the inventory management for the staffs. The
collected average values and count of the quotes made is being used for the introduction for
this automated system in manner to allow the automation for the staffs.
For Staffs
Material Size Quote Acceptance
Silk S Accepted

6
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
For Staffs
Popularity Quote Amount(in $) Stock Status Manufacture Item
Unpopular 453.65 Not in Stock Start Manufacture

7
ITECH1100 UNDERSTANDING THE DIGITAL REVOLUTION
Bibliography
McDonald, R. (2018). Data Exploration: Draft Syllabus.
Naseri, A., Khalilzadeh, H., & Sheykhizadeh, S. (2016). Tutorial Review: Simulation of
Oscillating Chemical Reactions Using Microsoft Excel Macros. Analytical and
Bioanalytical Chemistry Research, 3(2), 169-185.
Santos, J. (2015). A self-assessment, and a tutorial in Excel. Septentrio Educational, (3).