Operating Costs Spreadsheet for Tuxedo Spike

The operating_costs.xlsx spreadsheet calculates the projected annual outgoing costs of running Tuxedo Spike. Input cells are in blue. Output includes total hours per week and year for each employee, as well as total projected costs.

Task 1
The operating_costs.xlsx spreadsheet calculates the projected annual outgoing costs of running
Tuxedo Spike
Assumptions
 All employees work 52 weeks in a year.
 Each employee has 4 weeks paid leave i.e they are paid 52 weeks
 3 casual replacements are expected to work for 4 weeks.
Input
Input cells are in blue. The spreadsheet is configured such that the working hours, hourly rates,
and fixed and utility costs can be varied.
Output
The table below shows the total hours per week and year for each employee.
Employee
Total Hours Per
Week
Total Hours
Per Year
Arthur 25.50 1,326.00
Bhaljeet 15.00 780.00
Cassandra 25.50 1,326.00
Three casual replacements 66.00 264.00
The table below shows total projected costs at $109,096.00
Amount per annum
Fixed costs $ 9,100.00
Utility costs $ 3,120.00
Salaries $ 96,876.00
Projected costs $ 109,096.00
Task 2

Approach
To determine the staff member allocated to each entry, the following steps were performed.
Step 1: Determine actual Weekday
-Column F calculates the actual weekday for each quote using excel formula, TEXT(quote
date,"dddd")
Step 2: Determine Actual Staff member
- Arthur and Bhaljeet work together to manufacture tuxedos. Furthermore, Arthur works
Monday to Wednesday, while Bhaljeet works Thursday to Friday.
-Column F determines the actual staff member using a Nested IF function based on the specific
days that Arthur and Bhaljeet work.
Findings
How some aspect of quoting or manufacturing has changed over time
The graph below shows how quote and manufacturing prices are changing over the four months.
This graph was created using Excel’s pivot chart function based on average amounts.
2018-06-08
2018-06-22
2018-07-06
2018-07-20
2018-08-03
2018-08-17
2018-08-31
2018-09-14
2018-09-28
0
50
100
150
200
250
300
Average of Manufacturing cost ($)
Average of Quote amount
Amount in $
From the graph we observe the following:-

 Quoted costs are higher than manufacturing costs- this is expected as the company needs
to make a profit and meet its annual projected costs.
 Both the quoted and manufacturing costs drop sharply towards the end of each month.
How the quote amount differed for each staff member
The graph below shows how the quoted amount differed for each staff member over the four
months. This graph was created using Excel’s pivot chart function based on average prices.
Arthur Bhaljeet
-
100.00
200.00
300.00
400.00
500.00
600.00
700.00
Cotton
Leather
Silk
Tweed
Wool
Average Quote in $
From the graph we observe the following:-
 On average, quotes from Arthur are higher than Bhaljeet across all materials and sizes.
 Arthur prices leather higher than wool, whereas Bhaljeet does the opposite.
 Silk material is the most expensive
 Cotton material is the least expensive.
 Size XL prices are generally higher than other sizes.

Task 3
The approach used was to base the quote amount on historical price averages.
Using Excels Pivot, a table was created to show the average price for each material and size.
This information is summarized in the table below.
Average price based on historical data
L M S XL XS XXL
Cotton 80.84 75.84 76.67 85.87 64.5 88.32
Leather 212.64 206.3 184.85 179.89 149.95 227.21
Silk 482.28 338.67 429.93 556.04 476.76 628.13
Tweed 218.975* 230.05 197.825* 207.9 165.6 277.33
Wool 141.21 143.63 151.95 130.58 122.39 186.75
In the automation worksheet, the user inputs the size and material. Based on this, the average
price is returned. For example the average price of a Leather Tuxedo in size S is $184.85.
*Note there was no historical price information on Tweed material for sizes L and S. Therefore,
an assumption has been made as below:
 For size L, the price was calculated as the average price of the Tweed size M and XL. i.e
$218.98.
 For size S, the price was calculated as the average price of the Tweed size M and XS. i.e
$ 197.83

Task 4
Similar to Task 3, the prices were based on historical price averages. To determine the popular
and unpopular items, Excel count function was used to assess the number of quotes received for
each fabric and size. This information is summarized in the table below.
Number of Orders based on historical data
L M S XL XS
XX
L Total
Cotton 8 8 7 18 9 5 55
Leather 7 7 8 7 11 7 47
Silk 4 6 6 8 3 6 33
Tweed 2 1 1 3 7
Wool 11 8 6 8 19 12 64
Total 30 31 27 42 43 33 206
From the table, it is observed that Silk and Tweed materials had the least orders- these were
classified as ‘unpopular’ fabrics across all sizes. Cotton, Leather and Wool were classified as
‘popular’ across all sizes.
In the automation worksheet, the user inputs the size and material. Based on this, a quote is
returned. In addition to the quote, the calculator also returns whether they should proceed to start
manufacturing.