Analyzing Sock Basement's Business Processes with Excel Automation

This assignment focuses on automating business processes for the Sock Basement, a small clothing manufacturer, using Microsoft Excel. The student's solution includes calculating employee wages, annual outgoing costs, and visualizing quote amount variations over time using line charts and pivot tables. The assignment also involves creating a swim lane diagram for process improvement and developing an automated system to display quote prices based on material and size, using historical data. Furthermore, the project implements a system to check the popularity of unique categories and manage inventory, displaying relevant information to staff members. The solution demonstrates the application of Excel for data analysis, visualization, and process automation to improve efficiency and decision-making within the business.

Task 0:
Sock Basement is small clothing manufacturer company and manufacturer socks of different
kinds. An automated solution of evaluation of quote amount of different types of socks are
needed to be produced in a report format. Additionally, the costing and expenses on yearly
basis of the company is needed to be shown. The 3 staffs who are working in this company
are Archie, Bertha, and Celia. Archie, Bertha produces socks and sales, orders and delivery
are managed by Celia.
Now, in the following table the name of employee and their working hours in a week is
represented below.
Name Working days Working hours(per
week)
Additional Working
hours(per week)
Archi
e
Monday, Tuesday and
Wednesday
22.5 3
Bertha Thursday and Friday 15 0
Celia Every weekday 22.5 3
Task 1:
The wages for the employees (including the replacement employee) in a year is calculated
using excel for respective staff members’ as given below. In addition the yearly utility cost
and the fixed cost is combined with the yearly wage cost for calculation of the Annual
outgoing cost using excel.
Now, the wages for the employees including the replacement employee is calculated with
suitable formulas in excel.

Name Specific Working days Working hours(per week) Additional Working hours(per week) Total Working hours (per week) Hourly rates(in $) Annual cost(in $)
Archie Monday, Tuesday and Wednesday 22.5 3 25.5 26 34476
Bertha Thursday and Friday 15 0 15 26 20280
Celia Every weekday 22.5 3 25.5 24 31824
Employee as a replacement 66 39 10296
Yearly Fixed cost(in $) 9100
Annual Utility cost(in $) 3120
Annual Total Outgoing cost(in $) 109096
So, the total outgoing cost of the Sock Basement is $109096.
Task 2:
The variation of quote amount with respect to time for all materials and sizes is given below
by the following line chart.
The manufacturing cost variation over time is shown below by the following line chart.

Now, for showing the quote amount variation for three different staff members the average,
standard deviation and number of each unique category of material and size combination is
produced by excel pivoting through a column chart as shown below. In the pivot table the
variation of the quote amount is shown.

From the above column chart it is clear that the average and the standard deviation of
SilkXXL category or cloths made using silk and are of XXL size has varied maximum in
quote amount from staff to staff.
Task 3:
Now, for the process improvement and maintaining equal quote price for each unique
category a swim lane diagram is developed with the help of staff members as given below.
Based on that swim lane an automated system is developed in excel in which by choosing the
material and size of garment as asked by customer the quote price will be displayed to the
staff member which is the average quote price of that category obtained from historical data.

Sample run:
Display to staff members
Material Size Quote Amount(in $)
Cotton XXL 90.38
Staff members Usage
Task 4:
The process is now improved where the popular and not popular unique categories (material
and size combination) as ordered by customers are automatically checked and if it is equals
or over a certain limit (here it is considered 8 in historical years) then the category is popular
otherwise not popular. The quote amount is displayed to the staff members which is the
average amount of that category in historical years. If customer accepts quote amount then

inventory is checked and start manufacture is displayed only when the item is not in the
inventory. The inventory is modelled by random choosing between in stock and not in stock.
Sample run:
Material Size Quote Acceptance
Leather XXL Accepted
Popularity Quote Amount(in $) Stock Status Manufacture Item
Popular 225.7125 In stock Do not Start Manufacture
Staff members Usage
Automatic Display to Staff Members