ITECH1100 Digital Revolution: Scarf Basement Excel Automation Project

This project provides an automated solution for Scarf Basement using Microsoft Excel to address various business challenges. The solution encompasses calculating staff wages, analyzing fixed costs, visualizing manufacturing cost variations over time, and maintaining price consistency through a swim lane diagram for quotation automation. Key features include tracking employee working hours, automating wage calculations, generating graphs to monitor manufacturing costs, and implementing a system for consistent price quotations based on material and size. The project also incorporates process improvements by adding fields for popularity, stock status, and manufacturing triggers, enhancing overall efficiency and decision-making within the organization. This assignment provides a practical application of Excel in solving real-world business problems.

Running head: UNDERSTANDING THE DIGITAL REVOLUTION
Assignment 1 – Scarf Basement
Name of the Student
Name of the University
Author’s Note

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

2
UNDERSTANDING THE DIGITAL REVOLUTION
Task 0 – Setting Up
The assignment is created for the development of an automated solution for the Scarf
Basement organization such that the solution can be used for mitigation of the different
business problems. The problems in the business are identified and the IT solution is
developed in Microsoft Excel for its application in the current business process and mitigate
the problems. The basic logics used for the programming are analysed and the basic
principles of programming are used for the management of the business flow and processes.
For the development of the IT solution the team working in the organization is needed
to be identified along with their responsibility. The working shift timing and the extra work
done by them are also recorded for the calculation of the wages. The fixed costs are also
analysed for the management of the rent and the other utilities related with the current
business process of the organization. Currently the organization works with the three team
members Amy, Barnardo and Christina and Amy works on Monday, Tuesday and
Wednesday for a duration of 7.5 hours. Barnardo works on Thursday and Friday for a
duration of 7.5 hours and Christina as a part time employee works every weekdays for 4.5
hours. There are some fixed costs such as insurance and utility cost that are also calculated
for the proper working the IT solution prepared in excel.
The following table is created for the recording the details of the working hours and
the extra works done by them per week.
Name Working days Working
hours(in
week)
Additional Working
hours( in week)
Total Working
hours (in week)
Amy Monday, Tuesday 22.5 3 25.5

3
UNDERSTANDING THE DIGITAL REVOLUTION
and Wednesday
Barnard
o
Thursday and Friday 15 0 15
Christin
a
Every weekday 22.5 3 25.5
Task 1 – Cost of Operating the Business
The following table is calculated in Microsoft Excel for calculating the wages for the
staffs and a replacement worker is added using formulas. The expenditure for the annual and
the fixed expenditure are added for calculating the annual cost.
Name Working
days
Working
hours(in
week)
Addition
al
Working
hours( in
week)
Total
Workin
g hours
(in
week)
Rate per
hour ($)
Annual
Cost ($)
Amy Monday,
Tuesday and
Wednesday
22.5 3 25.5 26 34476
Barnardo Thursday
and Friday
15 0 15 26 20280
Christina Every
weekday
22.5 3 25.5 24 31824
Replacement 66 39 10296
Annual Fixed 9100

4
UNDERSTANDING THE DIGITAL REVOLUTION
cost(in $)
Annual Utility
cost(in $)
3120
Annual Total
Outgoing cost(in
$)
109096
Here, The total cost for running Scarf Basement is 109096 $.
Task 2 – Order Information
An excel graph is generated from the report that helps in identification of the change in cost
of the manufacture and it is demonstrated with respect to time.
2018-06-04 2018-06-26 2018-07-11 2018-08-01 2018-08-13 2018-08-24 2018-09-11 2018-09-20 2018-10-03
0
100
200
300
400
500
600
700
800
Quotation of variation on time
Date
Quote amount ($)

5
UNDERSTANDING THE DIGITAL REVOLUTION
2018-06-04 2018-06-25 2018-07-10 2018-07-31 2018-08-09 2018-08-23 2018-09-06 2018-09-18 2018-09-27
0
20
40
60
80
100
120
140
160
180
Manufacturing cost ($)
Date
Manufacturing Cost ($)
Thus for each of the unique combination of the different categories of materials the difference
in the amount of quote for each of the staff members is identified by the generation of the
mean standard deviation and for each of the category of quotes. Pivot table is used for the
demonstration of the mean standard deviation and demonstrated in the following chart of the
column.
CottonL
CottonS
CottonXS
LeatherL
LeatherS
LeatherXS
SilkL
SilkS
SilkXS
TweedL
TweedS
TweedXXL
WoolM
WoolXL
WoolXXL
0
100
200
300
400
500
600
700
StdDev of Quote amount
Count of Quote amount
Average of Quote amount

6
UNDERSTANDING THE DIGITAL REVOLUTION
From the above graph it can be identified that the Silk S types of garments has the
maximum variation of quote given by the three staff members and the average of the quote
amount is highest for the Leather S while it have less variation of the quote amount.
Task 3 – Price Consistency
For maintaining a consistency in the price of each of the category of items produced in Scarf
basement company a uniformity is needed to be maintained for the quotation of the material
depending on its size and material. The following swim lane diagram is created for the
automation of the quotation display by the staffs. The swim lane diagram helps the IT
development team to identify the needs and develop the information system to align the
business requirement such that no error quotation is generated that can affect the productivity
and growth of the organization.

7
UNDERSTANDING THE DIGITAL REVOLUTION
In the above diagram the staffs members are allowed to choose the material and it s
respective size depending on the customer requirement and the system should return the
amount quoted for the category of material selected. Behind the result the amount displayed,
the average quote for each the selected category given by the staff member are calculated
based on the historical data.
Sample
Task 4 – Process Involvement
For the improvement of the process different fields are added in the calculation depending on
the demand of each of the item sold by scarf basement organization. New tabs are added in
the table such as popularity, material, size, Quote acceptance, Quote amount, Stock Status
and Manufacture Item. A drop down list appears for the selection of the item from the
material list and the respective size is also needed to be selected for identification of the
acceptance or unacceptance of the quote. If the quote is accepted a checking is done that if
the item is available in the stock and if the item is not present in stock a message appears to
start the manufacturing process. For the starting the manufacturing process the minimum
amount present in the stock is checked and if the value is less than 10 the message start
manufacture is displayed to the staff.

8
UNDERSTANDING THE DIGITAL REVOLUTION
Sample:

9
UNDERSTANDING THE DIGITAL REVOLUTION
Bibliography
Blayney, P., Kalyuga, S., & Sweller, J. (2015). Using cognitive load theory to tailor
instruction to levels of accounting students' expertise. Journal of Educational
Technology & Society, 18(4), 199.
Marques, R. P. (2015). Dynamic Stock Management.
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.
Noori, H., Turabi, I., & Ajdar, M. (2017). Investigating the Roles of Shareholders and
External Board Directors on the Earnings Management: Evidence from Companies in
the Tehran Stock Exchange. International Journal of Economic Perspectives, 11(1),
1083-1093.
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.