Understanding Digital Revolution: Suit Design Excel Automation Project

This assignment focuses on automating and analyzing business processes for the fictional company, Suit Design, using Microsoft Excel. The tasks include creating an e-portfolio, calculating operating costs considering staff salaries, replacement rates, and utility expenses. Furthermore, the assignment involves data visualization using pivot charts to represent quote amounts and manufacturing costs over time, and to show the distribution of quote amounts by staff members. The analysis extends to determining average quote amounts based on material size and providing decision-making instructions regarding production based on product popularity and availability. The solution leverages Excel formulas, pivot charts, and conditional logic to provide insights into the company's operations.

Task 0
The Suit Design is a cloth manufacturing company and their staff handles the orders,
sales and the delivery of the orders. The three staffs who manages these sections are
Bhavneet, Albert and Claudia. The first task is to setting up an e-portfolio of the staffs with
the help of their work shift information. The yearly working hours and working days are
calculated using basic excel formulas.
Figure 1: e-portfolio
Source: created by author
Task 1
This task calculates the operation cost of the Suit Design by the company expenses.
The expenses are provided in the case study and the working rate of the Albert. Bhavneet and
Claudia. The yearly income of these three is calculated by adding and multiplying some data
in excel. The replacement t is allowed for paid, is about 4 weeks so according to the
replacement rate and the 4 weeks the yearly expense in replacement is also calculated. Then
the utility cost is multiplied by 12 to get the yearly utility cost as it was provided on the
monthly basis. At last al the total cost and incomes of the staff is added to get the total
operating cost of Suit Design.

Figure 2: Operating cost Calculation
Source: created by author
Task 2
This task is about the visually representation of the changes in quote amount over the
time. Chart is prepared using the Pivot chart in excel and the order is counted plotted
according to the date. The chart is represented as line chart for better representation.
Second, the changes of the manufacturing is also shown according to the dates and
using pivot chart feature in excel. Line chart is used for seeing the variations of
manufacturing date wise.
Figure 3: quote cost over time
Source: Created by author

Figure 4: manufacturing cost over time
Source: created by author
The next chart is also created using pivot chart to show the distribution of the quote
amount according to the staff members. First the day of the work is fetched from the date in
excel. Second the day is used for fetching the name of the staffs worked on that day. Later the
distribution of the quote amount is shown that how it differs for each staff member.
Figure 5: quote amount differentiation
Source: created by author

Task 3
The task is to show the estimated average quote amount of the materials according to
their size. This shows the price consistency of a product in the manufacturing company. The
list of material and size is sued as source. The main calculation of the price is done in excel
using the formula. The formula takes the material input and size input, then checks the price
where the input matches with field. The fields are then calculated to get the average roundup
price of that particular material and size.
Figure 6: List and the average roundup quote amount
Source: created by author
Task 4
Last task, is to show the decision of the manufacturing instructions. If the product is
popular with having the availability and not popular whether available or not then the
instruction is not to proceed with the production. If the item is popular and not available in

the stock then the instruction is to start the production of that particular material according to
the size also.
Figure 7: List sources
Source: Created by author
Figure 8: Instruction about Production
Source: Created by author