ITECH1100 Assignment: Maternitywear Revival Automation Project

This assignment focuses on automating business processes for the Maternitywear Revival Company using Microsoft Excel. The project begins with calculating the company's total operating costs, considering employee details, rent, insurance, and other utilities, with a total operating cost of $100960. The next task involves data visualization, using pivot tables and charts to analyze quote amounts and manufacturing amounts over time for different materials like silk, tweed, leather, and wool. The visualization also differentiates quote amounts based on employee working days and material sizes. The third task addresses price consistency, calculating average quote amounts for materials based on size, and uses pivot tables for analysis. The final task automates manufacturing based on the popularity and availability of materials, creating tables for popular and non-popular items, and managing inventory based on quote acceptance and inventory checks. The project includes figures illustrating each step of the analysis and automation process.

Running head: UNDERSTANDING THE DIGITAL REVOLUTION
UNDERSTANDING THE DIGITAL REVOLUTION
Name of the Student
Name of the University
Author Note

1UNDERSTANDING THE DIGITAL REVOLUTION
Task 1 – Operating Cost
The operating cost of the Maternity wear Revival Company is based upon the yearly
expenses made by the company. For this calculation the employee working details, replacement
details, company’s rent and insurance detail along with the other utility costs have been taken
into the account. There are other type of data is also present such as, overtime and pay along with
the differentiations of the employees on the basis of full time, part time or a replacement. The
data have been represented in the Excel sheets and with the help of formulas the total of the costs
have been calculated. The total calculated operating cost of the company is $100960.
Figure 1: Total Operating cost of Maternity wear Revival
Source: Created by author
Task 2 – Visualization
The Visualization of the data has been done in two separate ways. The Figure 2
represents the visualization of the quote amount and manufacturing amount of the materials

2UNDERSTANDING THE DIGITAL REVOLUTION
according over a certain period of time. This analysis and visualization is done with the help of
pivot table and chart. The chart shows the average quote amount and average manufacturing
amount for the each material over time. The chart has been represented using the bar graphs. It
has been analyzed from the graph is that silk has the highest amount of manufacturing and
quotation. Then comes the Tweed material and then Leather. It also shows that wool material has
been sold mostly over time and tweed very much less.
Figure 2: Changes in Quote and manufacturing amount over time
Source: Created by author
On the second visualization, the differentiation of the quotation amount for the
employees. The employees has been categorized on the basis of their working days in a week.
Using formula “=TEXT (A202,"DDD")”, the day have been obtained from the dates and the

3UNDERSTANDING THE DIGITAL REVOLUTION
employee has been categorized, as Archie and Connor worked on Monday and Tuesday whereas
Bernard and Connor worked on Wednesday, Thursday and Friday. The below visualization is
filtered according to the materials as well as their sizes. The quote amount here is shown as
average and it can be seen that the quote material of the silk of size XXL is the highest when
Archie and Connor were working which can be on Monday or Tuesday. Also the bar lines
representing Tweed material is also absent in some sizes for the quote amount.
Figure 3: Differentiation of quote amount over employees
Source: Created by author
Task 3 - Price consistency
Figure 4 represents the Price consistency as quote amount of the Materials according to
their size. The consistency has been calculated by averaging the total of the quote amount for a
particular material and size. For this calculation two field has been taken and formatted as list to

4UNDERSTANDING THE DIGITAL REVOLUTION
get field values from a source. Then the average is found with the help of the pivot table. Later
the material name and size is matched to the list field of the consistency table. Then the quote
amount field provides the averaged value which is sourced from the pivot table. Figure 5
represents the source table for the list fields of material and sizes along with the Pivot table. The
calculation outputs the silk (size XL) having highest average price and cotton (Size XS) for
having lowest average quote amount. The Price consistency table is later used in to justify the
automation table creation.
Figure 4: Price Consistency of Materials
Source: Created by author

5UNDERSTANDING THE DIGITAL REVOLUTION
Figure 5: Average Price Consistency of Materials
Source: Created by author
Task 4 – Automation
The automation of the manufacturing is based on the popularity and availability of the
material of a particular size. The flow of the calculation is step based process. First the Price
consistency table is generated and popularity of the product is detected. If a material is sold more
than in the recent then it is said popular otherwise non-popular item. If the item is popular then
the quotation amount is send in the popular item table otherwise in the non-popular table. In both
the tables, the delivery and manufacturing of the materials depend on the acceptance of quote
amount and checking of inventory (for popular items only). In case of manufacturing the check
inventory needs to be “No” and for delivering the quote needs to be accepted for popular items.
For non-popular items’ delivery, quote amount needs to be accepted.

6UNDERSTANDING THE DIGITAL REVOLUTION
Figure 6: Automation of Inventory management
Source: Created by author