Automated Evaluation of Quote Amount and Yearly Expenses for Sock Basement

This document provides an automated solution for evaluating the quote amount and yearly expenses for Sock Basement, a clothing manufacturer company. It includes the calculation of wages for employees, analysis of yearly costs, variation of quote amount with respect to time, and process improvement for maintaining equal quote prices. The document also includes a swim lane diagram and an automated system in Excel for displaying quote prices based on customer requirements.

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