Sock Revival: Excel Analysis and Business Automation Project

This project focuses on analyzing the operational and sales data of Sock Revival, a custom sock business, using Microsoft Excel. The project begins with calculating the company's total annual operational cost, including employee salaries, insurance, and utility expenses. Subsequently, the analysis delves into the sales data to identify trends and patterns. Charts are utilized to visualize the sum of quote amounts by month, revealing peak and low sales periods. The project further explores profit trends over time and average profit per material type, providing insights into product performance. Task 3 involves creating an Excel-based quoting tool, allowing employees to input material and size to generate a relevant quote based on historical sales data. Finally, the project introduces an automated decision-making table that considers product popularity and availability to guide production decisions, optimizing resource allocation for Sock Revival. The project uses formulas, charts, and data analysis to provide actionable insights and improve business processes. The project demonstrates the use of Excel for automating business processes and visualizing the impact of the automation.

Running head: DIGITAL REVOLUTION FOR SOCK REVIVAL
Digital Revolution for Sock Revival
Name of the Student
Name of the University
Authors note

1DIGITAL REVOLUTION FOR SOCK REVIVAL
Task 0
Sock revival sales custom and one-of-a-kind socks according to the order details as
placed by the customers through their online store. It manages its business by three
employees; Andres, Bernard, and Connor. Andres, Bernard are skilled employees who
manages the manufacturing of the socks and Connor is responsible for the sales and delivery
of the products for Sock revival. Following is the shift details of the employees to provide an
over view about the operation of the business organization.
Nam
e Days
Day
Count
Shift
hours
Overt
ime
Total
Shift
Hour
And
res Mondays, Tuesdays and Wednesdays; 3 7.5 3 25.5
0
Bern
ard Thursdays and Fridays 2 7.5 15
0
Con
nor
Mondays, Tuesdays, Wednesdays,
Thursdays, Fridays and Saturdays 6 4.5 3 30
Task 1
In order to calculate the complete operational details of the business of sock revival,
the payable to the replacement worker, utility cost, insurance cost and finally the payable to
the three permanent employees. For the employees, standard six days working day is
considered for calculating the cost.
Following table depicts all the considered costs for calculating the operational cost for the
organization.
Name Days Day Shif Ove Total Pay Wek

2DIGITAL REVOLUTION FOR SOCK REVIVAL
Cou
nt
tho
urs
rti
me
Shift
Hour
/
Ho
ur
ly
Pay
Andres
Mondays, Tuesdays and
Wednesdays; 3 7.5 3 25.5
$
26.0
0
$
663.
00
0
$
-
Bernard Thursdays and Fridays 2 7.5 15
$
26.0
0
$
390.
00
0
$
-
Connor
Mondays, Tuesdays,
Wednesdays,Thursdays,
Fridays and Saturdays 6 4.5 3 30
$
24.0
0
$
720.
00
Replacement
worker for 4
Weeks 70.5
$
39.0
0
$
2,74
9.50
Yearly
Payable to
Employees
Andres
$
34,476.00
Bernard
$
20,280.00
Connor
$
37,440.00
Replaement
Worker
$
10,998.00
Total
$
1,03,194.00
Insurance
Cost
$
9,100.00
Utility
cost($260/Mo
nth)
$
3,120.00
Total
Operational
Cost
$
1,15,414.00

3DIGITAL REVOLUTION FOR SOCK REVIVAL
From the above table it can be stated that, total annual operational cost for the Sock
revival is $1,15,414.
Task 2
In order to explore the insights from the available past sales record the available chart
in excel are utilized. In the first chart, the sum of quoute amount is compared by different
months of the years. The months are determined using the “TEXT” formals with the help of
the date available in the dataset.
Jun Jul Aug Sep Oct
0
2000
4000
6000
8000
10000
12000
Sum of Quote amount by Month
From the above chart it is clear that, the maximum amount of quote is done in the
month of August. Minimum amount of the quote for the products are recorded in the month
of October.
In the next part, the trend of profit is compared against the months. Followings is the
chart provided that depicts the trend.

4DIGITAL REVOLUTION FOR SOCK REVIVAL
Jun Jun Jun Jun Jun Jun Jul Jul Jul Jul Jul Jul AugAug AugAug Aug AugAug Aug Sep Sep Sep Sep Sep Sep Oct Oct
0
100
200
300
400
500
600
700
800
Profit compared by month
Profit
By observing the trend, it can be stated that minimum profit in the trend is in the
month of August and the maximum recorded in October. From the month June to August, the
trend of profit was low. On the other hand, from the month of September, the trend of profit
was quite high.
In the next part, the average profit for different socks made by different materials are
plotted.
Cotton Leather Silk Tweed Wool
0
50
100
150
200
250
300
350
400
Average of Profi t by Material
From the above chart, it can be stated that maximum average profit is for Silk material
and in case of cotton, the average profit is lowest.

5DIGITAL REVOLUTION FOR SOCK REVIVAL
Task 3
In this part, in order to ease the quoting process subject to the available sales data
following table is developed using the excel formulas. The employees will be able to select
material as well as size of Socks to get the Quote for the product. After the selection last row
will display a relevant quote that maintains the trend of profit according to the past sales.
SELECT
MATERIAL
Cotto
n
SELECT SIZE L
QUOTE PRICE 79.08
Task 4
As the business is a small organization thus it needs to use resources optimally in
order to maximize profit. In order to do that, depending on the availability and popularity of
the socks, following table is created. In this table employees will be able to select the
availability and popularity of products. The automated table will display the decision for the
Manufacturing of the Socks as shown below;
SELECT MATERIAL Cotton
SELECT SIZE L
QUOTE PRICE 79.08
SELECT POPULARITY POPULAR
SELECT AVAILABILITY AVAILABLE
PRODUCTION
DO NOT
START

6DIGITAL REVOLUTION FOR SOCK REVIVAL
Bibliography
Arifin, J. (2016). Microsoft Office Excel 2016 untuk Profesional. Elex Media Komputindo.
Jansen, B., & Hermans, F. (2018). The use of Charts, Pivot Tables, and Array Formulas in two Popular
Spreadsheet Corpora. arXiv preprint arXiv:1808.10642.
Jelen, B. (2018). Microsoft Excel 2019 Inside Out. Microsoft Press.
Lee, C. F., Lee, J., Chang, J. R., & Tai, T. (2016). Essentials of Excel, Excel VBA, SAS and Minitab for
statistical and financial analyses. Springer.
McFedries, P. (2019). Microsoft Excel 2019 Formulas and Functions. Microsoft Press.