BCS220 Management Information Systems -Decision support systems (DSS) . Assignment Teams of 2-3 12% Description: The following assignment is intended to be a brief introduction to how a spreadsheet package (Microsoft Excel) may be used as a Decision Support System. One of the classic DSS applications is “what if?” analysis, or the analysis of outcomes that may occur as a result of changing conditions in the business environment.
BCS220 Management Information Systems -Decision support systems (DSS)
To introduce you to this area, you are provided with an assignment that will allow you to model a business problem. You will identify relevant variables to put in your model, build the model in Excel, then perform a series of analyses to see what the outcomes would be under a variety of conditions in that business environment. Further, you will also use some of the features of Excel (e.g. charting) in order to communicate your results in a clearer fashion.
You should follow the instructions and submit 1 assignment in teams of 2 -3 students. The Situation: Gizmo produces vintage motorcycles. However, employee turnover rates are at an all-time high at Gizmo Manufacturing’s plants. The company is experiencing severe worker retention issues, which as leading to productivity and quality control problems. The majority of the company’s workers perform a variety of tasks paid hourly. The company currently tests potential applicants to ensure that they have the skills necessary for the intense mental concentration and dexterity required to fill the positions.Because of their employee’s issues, they also have problems understanding the financial and logistics aspects of their business.
Tasks: Step 1:
Since significant costs are associated with employee turnover, Gizmo Manufacturing wants to find a way to predict which applicants have the characteristics of being a short-term versus a long-term employee. Review the information that Gizmo Manufacturing has collected from two of its data sources. The tabs Step 1 contains information regarding employee retention. a.
Using Excel analysis functions seen in class, determine the employee characteristics that you would recommend Gizmo Manufacturing to look for when hiring new personnel. BCS220 Winter 2020 DSS Assignment b. Prepare a report based on your findings for your recommendations (which should include several forms of graphical representation) (Name the tab Report step 1, you may have other tabs for the analysis, name them appropriately).
Gizmo manufacturing is curious on how its profit will be affected by the sales over the next year.Prepare a best, worst, and most-likely scenarios for the sales over the next year. Use the data in tab “Step 2 Profit Scenario”. Name the new tab “Step 2 Scenario Summary”.
Gizmo is having a hard time making the business profitable. Knowing that you have great business knowledge and solid financial sense, Gizmo has come to you for your advice. They would like you to determine how many motorcycles they need to sell per year to break even. Use the data in tab “Step 3 breaking Even”. Copy a print screen of your What-if analysis tool you use to answer the question.
Gizmo’s motorcycle are manufacture at 3 plants (Denver, Colorado; Phoenix, Arizona; and Dallas, Texas) and are then shipped by truck to 5 distribution warehouses in Sacramento, California; Salt Lake City, Utah; Chicago, Illinois, Albuquerque, New Mexico, and New York City, NY. Since shipping costs are a major expense, management has begun an analysis to determine ways to reduce them.
Using the information in tab “Step 4 Shipping costs” minimize the costs of shipping goods from production plants to warehouses, thereby meeting the demand from each metropolitain area while not exceeding the supply available from each plant. Some business rules and requirements you should be aware of include:
Firstly, The problem presented, which involves the shipment of goods from 3 plants to 5 regional warehouses.
Secondly, Goods, which can be ship ed from any plant to any warehouse, cost more to ship over long distances than over short distances.
Thirdly, Focus on minimizing the total shipping costs.
Fourthly, Total shipped being less than or equal to supply at a plant.
Fifthly, Total shipped to warehouses being greater than or equal to the warehouse demand.
Lastly, Number to ship being greater than or equal to 0.