Automated Evaluation of Quote Amount and Yearly Expenses for Sock Basement

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.