ITECH1100 Automation of Business and Its Impact Assignment 1 Answer
Following is the screenshot of Operating Costs spread sheet:
- As can be seen above, various columns have been created for hourly rates and weekly hours so that any changes can be reflected easily. The amount column will reflect revised numbers automatically due to formula linkages.
- The revenue and costs numbers have been annualised basis 4 month data provided for the assignment. Revenue is based on ‘quote’.
- Salary accounts for almost two thirds of the total costs and indicates scope for reduction.
- It can be seen that at current scenario, the company is incurring losses ($20,104). It needs to increase revenue and/or decrease costs so as to make it a profitable venture.
Since they are mainly involved in manufacturing, the employees, Angus and Barry were assigned to various days basis their shift timings. Christina, a part-time employee takes care of sales and delivery and hence, was not assigned to any particular day (she works every weekday for a few hours).
The screenshots and graphs below indicate average manufacturing costs and quotes categorized basis employees:
It can be seen that the highest manufacturing cost is for silk socks and the least is for cotton socks. Further, the manufacturing cost does not differ much in case of Angus or Barry except that it is slightly lower in case of Barry.
It is obvious that when manufacturing cost is highest for silk socks, the quote will also be the highest. The logic holds true for cotton socks where both cost and quote are the least. However, since there is no set procedure for giving quotes, a huge difference can be seen in quotes from Angus and Barry. While Angus tends to quote a little above the overall average quote, Barry tends to quote lower than the average. The difference is rather glaring in case of leather and silk socks where he quotes vary by almost 100%.
The following graphs provide information about volume, sizes and gross profit with respect to variety of socks:
It can be seen that maximum quotes are for wool socks while they are minimum for tweed.
It can be seen that maximum average gross profit (average quote minus average manufacturing cost) is from Silk socks, followed by tweed socks. The gross profit is minimal from cotton or wool socks. Further, it can be seen that the gross profit does not vary much between various sizes of socks.
Seeing the manufacturing and gross profit pattern, it can be said that efforts should be made to increase sales of silk socks as they yield maximum margin. Also, there should be a process to streamline quote process so as to ensure consistent pricing.
The above is a screenshot of automation done for achieving pricing consistency:
- The automation has been done using a combination of formula such as, ‘Vlookup’, ‘Concatenate’, ‘Data Validation, ‘Pivot’, etc.
- Orange cells need input from employees. Some of the fields have dropdowns to achieve consistency in records.
- Grey Cells are automated cells and should not be plugged with any data.
- Once orange cells are filled in a row, ‘Historical Average Cost’ and ‘Historical Average Quote’ will get populated. The quote can be used for current order also. While, the historical cost is shown for convenience and a reference point.
- Once the rows are exhausted, the previous row can be copied and pasted so as to further extend the automation.
As can be seen in the above screenshot, automation has an added column, indicating ‘Popularity’. Basis provided data; number of quotes for different socks material and sizes was calculated through pivot. If the number is less than 5, item is deemed to be ‘Less Popular’ and if it is more than 5, then item is deemed to be ‘Popular’. This is a automatic field which will be generated as soon as orange fields are plugged in. The output of this column can be utilized to see whether the item should be manufactured immediately or post quote confirmation.
This will help the company to attain desired improvement whereby the popular items can be immediately manufactured so as to shorten the wait time. Even if a particular quote does not get confirmed, the inventory can be created for next quote which should be soon as the item is popular. If the above model provides output as ‘Less popular’, then it is advisable to wait for quote confirmation and then only go ahead with the manufacturing the items so as to avoid piling up of stock.