ITECH1100 Shirt Life: Business Process Automation with Excel

This assignment solution focuses on automating business processes for 'Shirt Life,' a clothing manufacturer, using Microsoft Excel. The tasks involve calculating operational costs, analyzing order information to compare staff performance and product pricing, creating an automated quote price calculator based on historical data, and identifying popular products to inform manufacturing decisions. The solution details the implementation of formulas and data analysis techniques within Excel to streamline operations, reduce inconsistencies in pricing, and optimize production based on demand. Graphs compare quote prices from different staff members, highlighting the need for a consistent pricing methodology. The assignment aims to demonstrate how Excel can be used to visualize and analyze data to improve business efficiency and decision-making. Desklib provides this assignment and many other resources like past papers and solved assignments for students.

Task 1: Costs of operating the business
Shirt life is a clothing manufacturer which provides products of several sizes made up of different
materials. The company has 3 employees as regular staff members. The members are paid on hour
basis depending upon time spent by them in the company. This is calculated as wage expenses to
the company and calculated in the table. The formula implementation allows for flexibility to alter
wage rates or hours spent in the company. The alterations are instantly reflected in the expenses
calculations. Other type of expenses incurred by Shirt Life are related to facilities being used and
rent for the place. This amounts to a small part of overall expenses but is adjustable. Any changes in
the expenses are reflected instantly in the calculated total annual expenses of the company.
Task 2: Order information
The company has kept record of orders received in the previous months. The data holds detailed
values for each quote price offered by the company. The details include data of the quote that
allows us to sort the staff member who made the sale. Then list also contains information about the
size and material of the product that was offered. For each product the manufacturing price and
Quote price are also stored. This information can be used to make comparisons for the operational
efficiency of the staff members. The company incurs a cost on product based on material used and
size of the finished product. This counts as the manufacturing cost to the company. The workers put
in their skills and labour to the material and produce a finished good that is sold at a price listed here
as quote price.
The side by side comparisons of the manufacturing costs and quote price of the products is shown in
graphs. The analysis is done for over a period of time for different sizes of the products. As can be
seen in the table and the graphs also, the quote prices are about 3 to 4 times the manufacturing
price. This indicates the company is making a good profit through sales of its products compared to
its input material costs.
The graphs here show the comparison of quote prices from Staff members April and Bhavesh. The
Quotes of April are comparatively higher than that of Bhavesh. However for some of the instances,
quotes from Bhavesh are higher compared to April. There is lot of inconsistency among quote prices
and not definite pattern. This means no particular methodology is used to set the prices for a
product to the customer.
Some mechanism is required to negate the effect of human intervention when quoting prices for
similar products to customers.
Task 3: Order information
The table calculates the average quote price for each product type listed in the history sheet of
quote prices. The products are categorized based on their size and the material used for production.
It is assumed that similar types of product should cost about same price in future as they were
quoted for in previous orders.

On this basis, the program expects user to provide product size and material required for the
product. The user is expected to enter only the size and material for the product he wants to order.
The excel program will automatically calculates the quote price that can be presented to the
customer. This will help reduce the difference in quote prices due to personal bias and help provide
consistent quote prices to customers. The suggested prices are calculated using lookup formula
searching the data in the table formed above using the given size and material as search criteria.
Task 4
Some products of the company are more popular compared to the other products. These products
can be filtered out from other products based on the count of quotes provided for each product
type. The table lists the times each product type was demanded by the customers in terms on
quantity.
Based on this, some products can be qualified to be more popular as compared to the other types.
The threshold of popularity can be chosen by the staff member by looking at the past sales count of
the product and can decide as product being popular. The member needs to fill answer as yes or no.
The program here shall only provide the count based on history of the product type chosen for the
current order. The decision to select product as popular or not relies with the staff member.
Member also needs to enter weather the item is in stock or not. Based on these inputs, a suggestion
is made to manufacture the product or not.