ITECH1100 - Jacket Scene: Automating Business Processes with Excel

This assignment focuses on automating business processes for Jacket Scene using Microsoft Excel. It includes tasks such as calculating employee wages, analyzing product costs and prices, suggesting quote prices based on historical data, and managing product inventory based on demand. The solution uses Excel functions to perform calculations, conditional averaging, and data lookups. The analysis highlights inconsistencies in pricing and proposes a system for more consistent quote generation based on product material and size. Furthermore, it addresses inventory management by tracking product demand and suggesting manufacturing decisions based on stock levels and customer orders. The assignment showcases the application of Excel in streamlining business operations and improving decision-making.

Task 1:
Jacket Scene employees has hired 3 employees to work at the company premises. The company is
operating from a rented location and has to pay annual rent for the place. The company pays $9100
as rent and $260 as monthly fees for utilities. This makes total facility expense for the company to
$12220. The company pays its 3 employees on basis of hours spent in the company. Andres and
Bethany are paid at $26 per hour and Carl is paid at $24 per hour. The hours spent by each employee
in the company and the wage rate are listed in the table. Excel allows to calculate the weekly and
yearly wages based on these inputs. Apart from these three employees, company also hires
temporary employees at $39 per hour for short duration of the year. They are paid as per hours they
spend in the company. This is also considered when calculating annual expenses for the company
and listed in the table. Adding up all expenses, the total annual expense for Jacket Scene is $109096.
Task 2:
The company Jacket Scene has sold products in various sizes over time. There is a cost associated
with manufacturing of each product and the price that is quoted to the customer for the same
product. There exists a difference between two values to account for labour and profit. Both the
metrics are calculated as average values over the period of time. This is done by using conditional
averaging functions in Excel. The products are selected for calculation of average if they belong to
particular time frame and size. This calculation is shown in the two tables above. The tables calculate
the average values of products selected using criteria listed in the header of the table and extreme
left column. The charts are made based on these data tables. Graphical presentation of the
quote prices and manufacturing prices are easier to compare. As visible the quote prices are about 3
to 4 times the manufacturing cost.
The quotes are provided by the staff members as they find feasible. There is no set of rules to
propose the quote price to customers. So the prices proposed have a lot of variation among two
staff members for the given sizes and time period. The price quotes given by Andres are way more
than that of Bethany most of the time. But there is no fixed ratio of increase or decrease in quote
prices. This points out to the fact, how individual staff members quotes vary from season to season
even for same product size.
Task 3:
The complete lack of relationship between quote prices suggested by two staff members make it
important to have a mechanism to provide quote prices that are more consistent. Idea is to provide
quotes that are based on average quote prices as suggested for products in the previous orders. The
table calculates the average quote price for a product type. The product type is based on the
material and size of the item. There are 6 different sizes from XS to XXL being made by the company
using up to 5 types of material. The user can enter the detail of the item being asked for by the
customer like its size and material. The Excel shall lookup the quote prices in the table above. The
suggested quote price can be used to indicator to suggest the customer a quote for the required
product. In case there needs to be some modifications to be done to the quote, the user can input
the difference amount in the box and new final quote price shall be calculated and shown at the
bottom of the option boxes.
Task 4:

Like in Task 3, the products are categorized based on its type of material and size of item. Each
product type has different sales volume. The table shown here calculates the total count of product
sold for each product category. The count is used as indication of products demand among
customers. If a product is in high demand, the product can be manufactured when customer has
shown interest in the product. If product hasn’t got a high demand, the product should not be
manufactured till the customer has confirmed the order. Since some products are manufactured
even before the customer confirm it. The store keeper needs to check stock before manufacturing
an item. If the item is available in the stock, the item need not be manufactured. If item is popular
and not available in stock, the item can be forwarded to production unit and stored till confirmed
order is received. This whole mechanism is implemented using set of option boxes below the table in
Excel sheet.