Project: Analyzing Business Costs, Sales, and Process Improvement

This project provides a comprehensive analysis of a business's operational costs, sales performance, and process improvement strategies. Task 1 focuses on calculating the yearly operational costs, including employee salaries, rent, insurance, and utilities, using an Excel sheet for easy editing. Task 2 presents line graphs illustrating the changes in manufacturing costs over time for different materials and sizes. Task 3 involves analyzing price consistency using a pivot table to determine average quote amounts for various sizes and materials, and then evaluating the potential profits based on historical orders. Finally, Task 4 assesses product popularity based on order counts and utilizes a swim lane diagram to optimize production processes. The analysis covers financial aspects, sales strategies, and operational efficiencies to improve overall business performance.

Task 1 - Costs of operating the business
Working Hours Hourly
Pay
Weekly
Pay
Working
WeeksMonday Tuesday Wednesday Thursday Friday Saturday
Andres 8.5 8.5 8.5 0 0 0 26 663 48
Bhavesh 0 0 0 7.5 7.5 1 26 416 48
Cindy 4.5 4.5 4.5 4.5 4.5 0 24 540 48
Replacement 6 6 6 6 6 0 39 1170 12
Total Salary 91752
Rent and Insurance 9100
Utilities Cost (monthly) 260
Utilities Cost (yearly) 3120
Total Operating Cost (yearly) 103972
The operating_costs.xlsx is an editable sheet for calculating the yearly operational cost, where
one can easily edit working hours, hourly rates, and fixed and utility costs for calculating the
operational cost. Angres and Bhavesh, as skilled manufacturing staff, cost $26 per hour to
employ. Cindy, a part-time employee, costs $24 per hour to employ. All staff are also entitled to
four weeks of paid annual leave, during which a casual replacement is required at a cost of $39
per hour. Fixed costs such as rent and insurance are $9100 per year, and utilities costs are $260
per month.
Task 2 - Sales team offers

The above two line graphs shows the change in manufacturing cost ($) over time for different
materials and sizes. The quoted price for Silk was highest and for Cotton was lowest. It is hard to

comment on trends in both the plots. We can observe that for most of the time the quote amount
for XXL size was highest than the other sizes.

Task 3 – Price consistency
Quote amount Size
Material L M S XL XS XXL
Cotton 86.3875 77.46 73.9125 77.625 61.38571429 90.94
Leather 151.1166667 208.8 163.2090909 185.5 145.7 194.1
Silk 418.9 467.45 379.15 578.8583333 450.0222222 302.75
Tweed 200.5 233
Wool 140.3333333 129.8909091 147.9916667 148.575 121.6285714 159.4916667
The above pivot table, made using Excel and can be found under Price consistency worksheet,
we can find the average quote amount for different sizes and materials of dresses. The above
table was used for providing the quote amount to the user. Now applying this approach to the
historical orders and the following table shows the descriptives of profits, the company would
have made if they had used these prices for quoting.
Profit
Mean 128.9663462
Standard Error 7.198309869
Median 95.55833333
Mode 126.6875
Standard Deviation 103.8155013
Sample Variance 10777.65831
Kurtosis 4.488487006
Skewness 2.247346999
Range 458.7666667
Minimum 43.18333333
Maximum 501.95
Sum 26825
Count 208
Largest(1) 501.95
Smallest(1) 43.18333333
Confidence
Level(95%) 14.1913984

We can observe that the mean profit on every order would have been $128.966 and minimum of
$43.1833 if the above prices were used for the quoting the price to the customers on different
historical orders.
Task 4 - Process improvement
Count Size
Material L M S XL XS XXL
Cotton 8 10 8 8 14 5
Leather 6 3 11 3 16 3
Silk 4 2 6 12 9 2
Tweed 1 2
Wool 9 11 12 8 14 12
The above table was used for deciding whether a product is popular or not. For example, the
number of order for wool dresses of size Large were 9 which is higher than the average number
of orders for different material and size. Once, it was decided whether a product is popular or
not, we used the swim lane diagram to decide whether we should instantly start the production or
not.