Costs and Sales Analysis

The assignment content discusses four tasks related to the costs of operating a business, sales team offers, price consistency, and process improvement. The first task involves calculating the yearly operational cost based on hourly rates, fixed and utility costs, and paid annual leave for three employees. The second task presents two line graphs showing the change in manufacturing cost over time for different materials and sizes. The third task provides a pivot table to find the average quote amount for different sizes and materials of dresses, which was used to calculate the mean profit on every order. Finally, the fourth task uses a count table to decide whether a product is popular or not, based on the number of orders received.

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.