A Decision Support System (DSS) for the iTab Company

Added on - 17 Oct 2019

  • 4


  • 1933


  • 109


  • 0


Showing pages 1 to 2 of 4 pages
A Decision Support System (DSS) for the iTab CompanyYou have been hired by management of the iTab Company to help determine a productionplan for the next year. After studying the information you have collected (see Attachment 1), youdecide to develop an Excel spreadsheet simulation (see Attachment 2 for a sample format).Specifically, you would like to use this application to address two issues:a.To recommend a production plan (Plan A) for the next 12 calendar months. Your plan mustmeet all the policy constraints as listed in Attachment 1 (e.g., hiring, firing, overtime,minimum workforce, demand, and minimum inventory). You have been given aminimumgross profit target for the next 12 months at $1,500,000.b.To study the impact of the policy that limits hiring to 30 percent, and firing to 10 percent, ofthe existing workforce. For this purpose, you will develop a production plan (Plan B) thatlets management 1) increase the wage rate from $12/hour to $14/hour and firing cost from$650 to $800 per worker, and 2) be allowed to hire or fire as many people as it feelsnecessary in any month (all other constraints still hold, including the minimumworkforce requirement). Management of iTab would like to see how a more flexiblehiring/firing policy would influence profitability. Not knowing the precise impact of thispolicy change, management has set a minimum 12-month gross profit target at $1.4 million.SUBMISSION REQUIREMENTS:Create the two worksheets in the same Excel workbook file and label them ‘‘Plan A’’ and ‘‘PlanB’’ (the thumb tabs at the bottom).A.Electronic (One submission per group):1.At the completion of your project, submit your Excel workbook file on the courseMoodlesite (Do NOT submit the memo). Name the file so it has the format:lastnamefirstname.xls, using one of your group member’s name. For example, for a studentnamed John Smith, the file name will be ‘‘Smith-John.xls.’’ Your submission will NOT beaccepted after 5 minutes past the class starting time on the date it is due.B.Hard copy (One submission per group):1.A one-page printout of your Plan A worksheet, including theAssumptions Section. Thegroup that manages to provide the highest total profit for the year with Plan A will receive a10-point bonus. The runner-up will receive a 5-point bonus.Under no circumstancesshould you submit a plan with a 12-month gross profit below $1,500,000.2.A one-page printout of your Plan B worksheet, including theAssumptions Section, Showyour best results with wage rate now at $14/hour and firing cost at $800 per worker, butwithout constraints on hiring or firing (a minimum of 20 workers must still be maintained).You must delete the rows labeled "Max Hire" and "Max Fire" (don’t justPage1of Project 1
hide them!). Your 12-month gross profit for Plan B, while UNLIKELY to reach$1,500,000, must be at least $1.4 million. The group that manages to provide the highesttotal profit for the year with Plan B will receive a 5-point bonus. The runner-up will receivea 3-point bonus.3.A printout of a cover memo (max. 2 pages, single-spaced) addressed to the CEO of iTabComputers. In the memo, compare the two production plans you developed and make arecommendation. Your memo should make references to your worksheets, and it shouldextract some key figures from your worksheets to support your argument. Please make surethat you use the correct format for a memo, which is different from that of a letter.4.Download and printProject2Grading Sheet.Fill out the names. Staple your hardcopies inthe following order: grading sheet, cover memo, Plan A worksheet, and Plan B worksheet.No late project will be accepted. Just turn in what you have for partial credit.Attachments:1. The iTab Company (Case)2. Sample WorksheetAttachment 1: The iTab CompanyThe iTab Company is a small manufacturer of tablet computers. In reviewing iTab’sfinancial records, management has discovered that the company’s production plan has been veryinefficient in the past. This resulted in either lost sales due to insufficient inventory or excessiveinventory carrying costs due to overstocking. It appears that the company needs to develop aproduction plan based on expected demand for its products, and on regular and overtime workforcelevels. Although multiple products are supplied in different packaging, management has determinedthat ‘‘units of tablet computers’’ be used as an aggregate measure of production capacity.Demand for the iTab products varies with the time of year. It peaks during the Christmasholiday season and trails off in the summer months. There is a spike in August, however, due to Back-toSchool sales. Forecasted demand (in units) for the next calendar year is given as follows:JanFebMarAprMayJunJulAugSeptOctNovDecTotal1,9001,7001,5001,3001,1001,2001,6001,9001,8001,5002,1002,50020,100The following cost and resource data have been collected:1.Each worker can produce a total of 60 units of tablet computers per month on 180 regularwork hours. For overtime, the same productivity is assumed, subject to a maximum of 15percent overtime constraint. For example, if there are 20 workers on payroll in a givenmonth, total overtime hours (Max OT hours) cannot exceed 540 hours (20×180×15%).Page2of Project 1
You’re reading a preview

To View Complete Document

Become a Desklib Library Member.
Subscribe to our plans

Download This Document