Automated Solution for Garment Oasis: Excel Assignment
Verified
Added on  2023/04/21
|7
|698
|121
AI Summary
This assignment provides an automated solution for the Garment Oasis using excel. It includes calculations for employee wages, annual outgoing cost, manufacturing cost variation, and swim lane diagram for process improvement.
Contribute Materials
Your contribution can guide someone’s learning journey. Share your
documents today.
Task 0: The automated solution for the Garment Oasis is produced in this assignment by using excel. The Garment Oasis is basically a small size clothing manufacturer that produces user specified and one-of a kind garments and sell the products through online store. There are total 3 staff members namely Arthur, Britney, and Cheryl working in different shift timings and different days. Arthur and Britney produces garments. Orders, sales and delivery are managed by Cheryl. The employee name and their total working hours in a week are represented in the following table. NameSpecific Working daysWorking hours(per week) Additional Working hours(per week) ArthurMonday, Tuesday and Wednesday 22.53 Britne y Thursday and Friday150 CherylEvery weekday22.53 Task 1: The wages for the employees (including the replacement employee) in a year is calculated using excel for respective staff members’ as given below. In addition the yearly utility cost and the fixed cost is combined with the yearly wage cost for calculation of the Annual outgoing cost using excel.
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
NameSpecific Working days Working hours(per week) Additional Working hours(per week) Total Working hours (per week) Hourly rates(in $) Annual cost(in $) ArthurMonday, Tuesday and Wednesday 22.5325.52634476 BritneyThursday and Friday 150152620280 CherylEvery weekday 22.5325.52431824 employee as a replacement 663910296 Yearly Fixed cost(in $) 9100 Annual Utility cost(in $) 3120 Annual Total Outgoing cost(in $) 109096
So, the total outgoing cost of the Garment Oasis is $109096. Task 2: The variation of quote amount with respect to time for all materials and sizes is given below by the following line chart. The manufacturing cost variation over time is shown below by the following line chart.
04-06-201818-06-201803-07-201816-07-201827-07-201808-08-201820-08-201829-08-201812-09-201825-09-201805-10-2018 0 20 40 60 80 100 120 140 160 Manufacturing cost ($) Now, for showing the quote amount variation for three different staff members the average, standard deviation and number of each unique category of material and size combination is produced by excel pivoting through a column chart as shown below. In the pivot table the variation of the
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
From the above column chart it is clear that the average and the standard deviation of SilkXXL category or cloths made using silk and are of XXL size has varied maximum in quote amount from staff to staff. While, the least variation excluding the categories which are sold only once in historical year is for CottonXXL. Task 3: Now, for the process improvement and maintaining equal quote price for each unique category a swim lane diagram is developed with the help of staff members as given below. Based on that swim lane an automated system is developed in excel in which by choosing the material and size of garment as asked by customer the quote price will be displayed to the staff member which is the average quote price of that category obtained from historical data.
Sample run: Display to staff members MaterialSizeQuote Amount(in $) WoolXL165.6125 Staff members Usage Task 4: Now, the above process can be improved in which the popular and not popular items as ordered by customers can be automatically detected based on a predefined level (here in the historical year the items sold to number of customers equals or more than 10 are considered
popular). Now, the company inventory is unknown and hence this is randomized in excel by in stock and not in stock status and based on that item manufacture is started. Sample run: MaterialSizeQuote Acceptance CottonLAccepted PopularityQuote Amount(in $)Stock StatusManufacture Item Popular79.68461538Not in StockStart Manufacture Staff members Usage Automatic Display to Staff Members