ITECH1100 Understanding the Digital Revolution: Sock Limited Analysis

This assignment analyzes the business operations of Sock Limited, a customized socks manufacturer, using Microsoft Excel to automate processes and visualize data. The solution encompasses several key tasks. Firstly, it calculates the company's total operating expenses, including employee compensation, replacement costs, and fixed expenses. Secondly, it examines the relationship between quote amounts and manufacturing costs by associating sales with manufacturing staff (Angela or Bhavesh) based on their workdays and using Excel's IF function and chart functionalities. The analysis includes pivot tables to compare quote amounts by staff and a pie chart for visualization. Thirdly, the assignment focuses on estimating quote prices based on material type and size, employing pivot tables, the CONCATENATE function, and VLOOKUP to determine minimum average prices. Finally, it identifies popular sock categories based on the number of quotes, using the COUNTIF function to categorize products and recommend immediate manufacturing for popular items, while advising to wait for unpopular items. The solution demonstrates practical application of Excel functions for business analysis and automation, aligning with the learning outcomes of ITECH1100.

Task 1:
Sock Limited, a customized socks manufacturer employs three people. While, Angela
and Cassandra works for 25.5 hours/week, Bhavesh is working for 15 hours per week.
Based on their hourly wages and no. of hours worked they are getting a total annual
compensation of $86,580. The company allows them 4 weeks of paid leave during
which the company hires a replacement at a wage of @$39 per hour, the cost for this
replacement is computed as $10,296. Apart from this, they incur a fixed cost
$12,220 on rent and utilities.
The total operating expense for the company is $109,096.
Task 2:
Since the company is involved in customized socks manufacturing indicating all
orders are unique and thus the company do not have a standard cost or price for the
products. We have analyzed the quotation and manufacturing data over time for the
company to identify its pattern over time.
To start with, we associated each sale with the manufacturing staff that is either
Angela or Bhavesh. We have done this using the information on the days of the week
on which they work. While Angela works on the Mondays, Tuesdays and
Wednesdays, Bhavesh works on Thursdays and Fridays.
We have used the Excel function of IF as below to identify the day of the sale from
the quote date and then associate with the respective staff.
“IF(OR(WEEKDAY(A2,2)=1,WEEKDAY(A2,2)=2,WEEKDAY(A2,2)=3),"Angela”
, Bhavesh")”
We then used the chart functionality of excel to create a line chart for the quote
amount and associated manufacturing cost to understand the profitability of the
company. The chart clearly indicated that the company’s quoted price was higher than
their cost but there was no specific pattern and no relation between them.
For understanding how quote amount differed between staff, we created a pivot table
to calculate the sum total of all the quotes that they made. Finally, a pie chart was
created to visualize the percentage of quote made by both the staff.

Task 3:
To estimate a quote price based on the type of material and size of the socks, we
started with creating a pivot table and averaging the quotes against each type of
material and their sizes. We used the averaging method where our minimum quote
amount should be greater than or equal to the average of all the historical quotes that
the company has made for that category.
After the pivot with average of the historical quotes was created, we used te
Concatenate function (Cheusheva, 2019) to join the respective texts of the material
and size. Once the data table was ready, we provided the user to enter his desired
material and desired size, basis, which excel would do a VLOOKUP in the data table
to reflect the minimum average price.
(Note: To create the list of for entering the material and size we used the data
validation ("Excel Data Validation Guide | Exceljet", 2019) functionality of the excel)
Task 4:
For the last task, we set the parameters for the popular items based on the number of
quotes sent for the product category. We considered all those products which were
quoted and sold more than 10 times in the past as p[popular.
We first used the Pivot table function of excel to count the no. of quotes that we
received for each category of socks that we manufacture. If the count was more than
10 the product was labeled as “popular” else “unpopular”. We used the CountIF
function of excel to categorize the products. Once the check for popularity was done,
we recommend “start manufacturing immediately” decision for popular items and
wait for unpopular items to save cost and time.

References
Cheusheva, S. (2019). CONCATENATE in Excel: combine text strings, cells and
columns. Retrieved 25 September 2019, from https://www.ablebits.com/office-
addins-blog/2015/07/15/excel-concatenate-strings-cells-columns/
Excel Data Validation Guide | Exceljet. (2019). Retrieved 25 September 2019, from
https://exceljet.net/excel-data-validation-guide