Assignment 1: Automating Suit Revival Business Processes with Excel

This assignment focuses on automating business processes and analyzing data using Microsoft Excel for a fictional company called Suit Revival. The student created a spreadsheet to calculate projected annual costs, incorporating fixed, utility, and salary expenses. The analysis included determining staff members associated with each quote and visualizing how quote and manufacturing prices changed over time, revealing trends and price differences. Furthermore, the student developed a quote calculator using nested IF and VLOOKUP functions to determine quote amounts based on material and size, and analyzed the popularity of different fabrics and sizes. The solution demonstrates the application of Excel formulas, data visualization, and logical functions to solve business problems and provide insights for decision-making.

Task 1
This spreadsheet calculates the projected annual outgoing costs of running Suit Revival.
Input
The spreadsheet is configured such that the working hours, hourly rates, and fixed and utility
costs can be varied by the user in the highlighted cells.
Assumptions
The following assumptions have been made
 There are 52 weeks in a year.
 Casual replacements are expected to work for 4 weeks.
 Monthly utility cost will be multiplied by 12
Output
The table below summarizes the annual projected costs of running suit revival
Description Annual Costs
Fixed costs $ 9,100.00
Utility costs $ 3,120.00
Annual Salaries $ 96,876.00
Total Projected costs $ 109,096.00

Task 2
Approach
To associate each quote with a staff member, the analysis below was performed on the raw data.
Determine actual Weekday
- Using excel formula, TEXT (quote date under column A,"dddd") ,column F allocates the actual
weekday for each quote entry entered by Conrad during the week.
Determine Actual Staff member
- We have been given that Alfred and Bhavesh work together to manufacture suits. Furthermore,
Alfred works Monday, Tuesdays and Wednesday, while Bhavesh works on Thursday and Friday.
- Using Excel’s IF function and the actual weekdays under column F, Column G allocates the
actual staff member associated to each quote entry.
Findings
How some aspect of quoting or manufacturing has changed over time
The chart below analyses how quote and manufacturing prices are changing over time. These
values are based on the average quoted amounts and manufacturing costs.

From the graph we see that it appears that in September, the quote prices of suits almost double
despite normal manufacturing costs. Furthermore, quoted costs are more than double the
manufacturing costs throughout the period.
How the quote amount differed for each staff member
The graph below shows how the quoted amount differed between Alfred and Bhavesh over a
period of time. This amount was based on their average prices across all sizes.
Alfred Bhavesh
-
100.00
200.00
300.00
400.00
500.00
600.00
Cotton
Leather
Silk
Tweed
Wool
Average Quote in $
From the graph we observe:-
 Silk material is the most expensive out of all fabrics with prices reaching up to $800.
 Cotton material is the cheapest fabric for under $100.
 On average, quotes from Alfred are higher than Bhavesh across all materials and sizes.

Task 3
To determine the quote amount, historical averages were used as a base. These figures are
summarized in the table below. As expected, the price of Silk is the highest based on average
quotes, whereas cotton suits are the cheapest.
Average of Quote amount
Material/ Size L M S XL XS XXL
Cotton 85.53 72.18 68.96 92.23 74.29 91.59
Leather 165.23 139.85 168.96 183.10 147.28 215.84
Silk 370.00 412.60 444.31 314.85 516.63 626.40
Tweed 214.10 228.50 230.77 210.35 231.60
Wool 135.00 126.89 122.17 166.92 110.22 167.86
Next the spreadsheet was configured such that size and material can be input by the user in the
blue cells.
Based on the input, the quote amount is returned in cell C10. I used a combination of Nested IF
and V lookup functions to arrive at the price.

Task 4
This task is an extension of task 3. Therefore historical averages were used as a base for the
quote amount
To assess the popular and unpopular fabrics and/or sizes, an Excel count function was used to
count 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 XXL Grand
Total
Cotton 6 9 16 7 10 7 55
Leather 4 4 7 5 20 5 45
Silk 3 5 7 4 3 5 27
Tweed 2 1 3 2 1 9
Wool 7 8 10 10 15 9 59
Grand Total 22 27 43 26 50 27 195
Popular and unpopular fabrics
From the table, it is observed that Silk and Tweed materials had the lowest orders- these were
classified as ‘unpopular’ in the fabrics category. Furthermore Size L was considered as
unpopular as it had the least amount of orders in the size category.
The spreadsheet was configured such that size and material can be input by the user in the blue
cells. Based on the input, the quote amount is returned in cell C10. In addition, depending on the
size or fabric, the calculator also returns whether they should proceed to start manufacturing in
cell C11.