Uniform Basement: Financial Analysis, Quoting, and Order Optimization

This assignment provides a comprehensive analysis of Uniform Basement's financial and operational performance. Task 1 focuses on projecting annual outgoings, considering fixed costs, utilities, and staff salaries (including casual replacements). Task 2 involves determining staff allocation for quotes based on weekday and using Excel's pivot function to analyze quoting and manufacturing trends over time, including comparisons between staff members and fabric types. Task 3 details the approach to quote generation based on historical prices using Excel's VLOOKUP function. Finally, Task 4 assesses the popularity of different uniform items by analyzing the number of orders using Excel's COUNT function, providing insights into popular and unpopular items, and incorporating an automated alert system in the Excel sheet to notify the user to start manufacturing immediately if an item is popular. The assignment aims to optimize the company's financial and operational efficiency.

Task 1
Approach
It is assumed that all employees are paid for 52 weeks. Furthermore, it is assumed that 3 casuals
will be hired to replace the permanent staff while each is on paid leave for 4 weeks.
The spreadsheet has been configured such that the working hours, additional hours, working
days, hourly rates, and fixed and utility costs can be varied in the blue cells.
Findings
The projected annual outgoings for Uniform basement amount to $109,096.00. This figure was
arrived as the sum of below:-
 Annual fixed cost of $9100
 Monthly utility cost multiplied by 12
 Annual Salaries of 3 permanent staff- this is the total number of hours per week
multiplied by 52 then by the applicable staff rate.
 Annual Salaries of casuals- this is the sum of the total number of hours per week for each
employee multiplied by 4 then by the replacement rate.

Task 2
Approach
The objective is to determine the staff member allocated to each quote entry that was entered by
Cynthia in the data provided.
We do this by first determining the actual Weekday of the quote using the excel formula
Text(quote date,”dddd”) under column F.
It is given that Amanda and Bernita work together to manufacture uniforms. Furthermore,
Amanda works Monday to Wednesday, while Bernita works Thursday to Friday.
Therefore, based on the above information, for each weekday in column F, we can assign the
actual staff member using a Nested IF function under column G.
Finally, to analyze how some aspects of quoting or manufacturing has changed, a graph is
created, using Excel’s Pivot function, to show the average quoted and manufacturing amounts
quoted over a period of time. Similarly, to analyze how the quote amount differed for each staff
member, a graph is created to show the average quoted amount by each employee for each
fabric.
Findings
How some aspect of quoting or manufacturing has changed over time
The graph below shows how quote and manufacturing prices are changing over time period.

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 rise substantially at the end of June and
September.
How the quote amount differed for each staff member
The graph below shows how the quoted amount differed for each staff member for each fabric
based on average prices.
From the graph we observe the following:-
 On average, quotes from Amanda are higher than Bernita across all materials and sizes.
 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 the average historical prices for each
material and size.
Using Excels Pivot function, a table is created to show the average price for each material and
size. The table below summarizes this output. As expected, the average price of silk is higher
than other materials. Similarly, the average price of XXL fabrics is the higher than the rest.
Material /Size L M S XL XS XXL
Cotton 85.69 71.95 84.45 87.38 68.80 97.15
Leather 179.18 195.95 163.98 221.23 148.49 242.82
Silk 470.94 379.10 365.18 551.96 112.50 587.05
Tweed 245.95 272.25 282.40 288.95 219.30 232.00
Wool 130.22 123.55 112.94 173.79 128.23 156.49
Next, in the automation worksheet, the user inputs the size and material which are in the blue
cells. Once the user inputs this information, the average price is returned using a vlookup and if
functions in excel. For example the average price of a Uniform made from wool in size XS is
$128.23.

Task 4
Using Excel’s count function, the number of quotes received for each fabric and size is assessed
and analyzed to determine the popular and unpopular items.
Number of Orders based on historical data
Count of Quote amount
L M S XL XS XXL Grand Total
Cotton 10 10 12 8 12 2 54
Leather 5 6 9 4 18 6 48
Silk 5 8 6 9 1 4 33
Tweed 2 2 2 2 2 1 11
Wool 11 13 8 8 16 9 65
Grand Total 33 39 37 31 49 22 211
Unpopular Items
From the table, it is observed that Silk and Tweed materials had the least orders- below average-
these were classified as ‘unpopular’ fabrics across all fabrics. The rest of the fabrics are popular.
Similarly, sizes XL and XXL had the least orders- below average- these were classified as
‘unpopular’ fabrics across all sizes. The rest of the sizes are popular.
Output
In the automation worksheet, the user inputs the size and material. Based on this, a quote is
returned. In addition to the quote, if the item is popular items then the calculator alerts user to
start manufacturing immediately, otherwise, there is no alert.