Assignment 1 - Dress Haus: Automating the Quoting Process Analysis

This assignment focuses on automating the quoting process for Dress Haus, a custom dress manufacturer. The project involves analyzing sales data, calculating operational costs, and visualizing key performance indicators (KPIs) using Microsoft Excel. Tasks include determining operational costs, analyzing sales data to identify trends in pricing and profitability, and automating the quotation process using the AVERAGEIFS function. Additionally, the assignment requires creating a decision-making process for production based on material availability and popularity. The analysis includes the generation of charts and graphs to illustrate sales frequency, employee profit contributions, profit by material type, and profit trends over time. The goal is to provide insights for informed decision-making within the business, enabling employees to efficiently manage orders and optimize production strategies. The assignment also includes the analysis of employee wages, insurance, and utility costs to determine overall operational expenses. Finally, the project aims at automating the decision-making process of whether to start production or not based on the availability of the cloth material and its popularity.

Running head: ASSIGNMENT 1 - DRESS HAUS
Assignment 1 - Dress Haus
Name of the Student
Name of the University
Authors note

1ASSIGNMENT 1 - DRESS HAUS
Task 0
For this project the main objective is automating the quoting process for the Dress
Haus products which are custom made according to the customer requirement or mentioned
for the order. The organization has three employees Alice, Bethany, and Carol. Two skilled
employees Alice and Bethany manufactures dresses on the other Carol who is a part time
employee is responsible for managing orders, sales and delivery of the dresses to the
customers.
Following table shows the total working hours and the wage/hour.
Nam
e Days
Day
Count
Shift
hours
Overt
ime
Total
Shift
Hour
Alice Mondays, Tuesdays and Wednesdays; 3 7.5 3 25.5
0
Beth
any Thursdays and Fridays 2 7.5 15
0
Caro
l
Mondays, Tuesdays, Wednesdays,
Thursdays, Fridays and Saturdays 6 4.5 3 30
Task 1
Operating cost for the business is given by the following table that is also present in
the accompanied excel sheet.
Name Days
Day
Cou
nt
Shif
tho
urs
Ov
erti
me
Total
Shift
Hour
Pay
/
Ho
ur
We
kly
Pay
Alice
Mondays, Tuesdays and
Wednesdays; 3 7.5 3 25.5
$
26.
00
$
663.
00
0 $

2ASSIGNMENT 1 - DRESS HAUS
-
Bethany Thursdays and Fridays 2 7.5 15
$
26.
00
$
390.
00
0
$
-
Carol
Mondays, Tuesdays,
Wednesdays, Thursdays,
Fridays and Saturdays 6 4.5 3 30
$
24.
00
$
720.
00
Replacement
worker for 4
Weeks 70.5
$
39.
00
$2,7
49.5
0
Yearly
Payable to
Employees
Alice
$
34,476.00
Bethany
$
20,280.00
Carol
$
37,440.00
Replacement
Worker
$
10,998.00
Total
$
1,03,194.00
Insurance
Cost
$
9,100.00
Utility
cost($260/Mo
nth)
$
3,120.00
Total
Operation
al Cost
$
1,15,414.00

3ASSIGNMENT 1 - DRESS HAUS
In order to calculate the total operation cost the total 6 working days from Monday to
Saturday. The cost of replacement worker for 4 weeks is also considered along with the
utility cost, insurance cost.
Task 2
Following are some of the insights that are available from the provided history sales
data.
Following graph shows, the frequency of the ordered products with different price
ranges.
0
10
20
30
40
50
60
70
80
90 82
61
40
15
6 1 3 4 5 2 2 1
FREQUENCY OF QUOTE PRICE
30-100 100-170 170-240 240-310 310-380 380-450
450-520 520-590 590-660 660-730 730-800 800-870
pRICE rANGE
COUNT OF ORDERS
Here it can be seen that maximum number of orders are placed with in the price range
of $30-100.
In the next graph, the profit generated by different employees is compared.

4ASSIGNMENT 1 - DRESS HAUS
Alice & Carol Bethany &Carol
20153.9
7319.9
Sum of Profit by Employee
Here, it can be stated that, Alice and Carol generated maximum amount of profit.
In the next, graph the profit by cloths made of different materials is depicted.
Cotton Leather Silk Tweed Wool
0
2000
4000
6000
8000
10000
12000
3146.7
5810.4
11605
914.5
5997.2
Sum of Profi t by Material
From the above graph, it is evident that silk made cloths generated the maximum
profit and Tweed made cloths generated the least amount of profit.
In the last insight the trend of profit against the time period is calculated and
depicted. Here it can be seen that, the steady growth in the profit started 2018-08-20

5ASSIGNMENT 1 - DRESS HAUS
2018-06-04 2018-06-20 2018-07-09 2018-07-19 2018-08-03 2018-08-16 2018-08-28 2018-09-11 2018-09-27
0
100
200
300
400
500
600
700
800
Trend of Profi t
Task 3
In order to automate quotation by the employees of the organization the avergaeifs
function available in function is utilized. In this function the averagerange is set to the quote
value column in the provided sales history data and the following table is developed as
provided in the accompanied excel sheet.
Material for
Cloth Silk
Size for Cloth XL
Suggested Quote 391
The employee can select the size and material as mentioned by the customer for a
specific record.

6ASSIGNMENT 1 - DRESS HAUS
Task 4
In order to start the production of the cloths the employees needs to determine the
availability and the popularity of the cloth. In the last sheet of the excel, another table is
developed that automates the decision making process which is depicted below;
Material for Cloth Silk
Size for Cloth XL
Suggested Quote 391
Check and Select Availability
inventory UNAVAILABLE
Check popularity NOT POPULAR
Production Status
Do not Start
Production