ITECH1100 - Pants Life: Automating Business Processes with Excel

This project utilizes Microsoft Excel to automate and analyze business processes for a fictional company, Pants Life. The assignment focuses on calculating the cost of operation, visualizing order information, and analyzing quote and manufacturing amounts over time and by staff member. It involves ...Read More

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

1UNDERSTANDING THE DIGITAL REVOLUTION
Table of Contents
Cost of Operation.............................................................................................................................3
Order Information............................................................................................................................3
Quoting or Manufacturing over time...........................................................................................4
Quote amount differentiation for each staff member..................................................................4
Price consistency.............................................................................................................................5
Process improvement.......................................................................................................................6

2UNDERSTANDING THE DIGITAL REVOLUTION
Cost of Operation
Cost of operation is the total cost required for running a business. The total cost are
calculated using employees’ salaries, expenses and other components of the business. The
company Pants life has total operating cost of 100960 dollars. It is calculated using the annual
salaries of Aiden, Bernard and Clyde along with the annual replacement, expenses and fixed
costs. The calculation has been done considering the 52 weeks of the year multiplying by the
weekly expenses. The excel formulas has been used for this task. The below table shows the used
columns and total expenses of the company. The replacement has been considered for max 4
weeks only hence multiplied by 4 instead of 52.
Figure 1: Cost of Operation for Pants Life
Source: Created by author
Order Information
The order information for the Pants life are visualized by the representing how the quote
and manufacturing amount changes over the time and the differentiations of the quote amount for
the staffs. The below figure 2 and figure 3 represents the order visualization of Pants life.

3UNDERSTANDING THE DIGITAL REVOLUTION
Quoting or Manufacturing over time
The change over time of quote and manufacturing amount can be seen in figure 2. It is
clearly seen that the silk is the most highlighted and highest quote amount over time. The
visualization has been done with the help of pivot table and chart where the bar graph has been
chosen. The values shown sum of quote and manufacturing amount, axis for material and date.
The amount has been shown as sum and sorted by each of the sizes and materials. The lowest of
the quote amount is represented by the tweed material. There are several instances where a
specific material of specific size is not sold such as the tweed of sizes XL and S.
Figure 2: Quote and Manufacturing amount over time for Pants Life
Source: Created by author
Quote amount differentiation for each staff member
The differentiation of the quote amount for the staffs depends on the date of the sale and
working day of the staffs together. Using excel formula name of day has been extracted from the

4UNDERSTANDING THE DIGITAL REVOLUTION
quote date whereas the day has been used as deciding factor for who was present on that quote
date. Then the pivot table is used for generating the bar graph. The visualization supports the
variance of quote amount sorted by each material and sized for the pair of staffs present on that
day. The below image represents how the tweed has been the most non-popular material for all
the cases. Whereas the silk has been seen for the highest grossing material for Aiden and Clyde.
The values have been selected in the pivot chart is the sum of quote amount.
Figure 3: Differentiation of quote amount for staffs
Source: Created by author
Price consistency
The price consistency of the material is the most important spec of the company to
provide a standard value. However the quote amount is dynamic, the calculation will require
average of the prices. The average are calculated on the basis of the material and size together.
The “Roundup” formula is used for rounding the value to eliminate decimals. The data validation

5UNDERSTANDING THE DIGITAL REVOLUTION
is used to take input from a list referred to the source list as there multiple values of sizes and
materials. There after the value of the input is match with the respective columns to get the
average of quote amount and manufacturing cost. The tweed of size XL and S has no sales hence
the value gets undefined as the average cannot be calculated.
Figure 4: Price consistency
Source: Created by author
Process improvement
Process improvement for the Pants life is the mainly the inventory decision management
for the manufacturing and sales. The calculation has been done with the same procedure of price
consistency calculation. The scenario has the conditions such as, if the product is not-popular
then whether the product is available or not, then the instruction of manufacturing will be denied

6UNDERSTANDING THE DIGITAL REVOLUTION
and quote will not be sent. On other hand, if the material is popular and available then the
manufacturing will be stopped and quote will note be sent. Lastly, if the material is popular and
not available then the quote is sent and instruction is set to yes for starting new production of that
material. The below table represents the sources of the list used and the availability process of
the manufacturing.
Figure 5: Process Improvement for Pants Life
Source: Created by author