A Decision Support System (DSS) for the iTab Company
4 Pages1933 Words296 Views
Added on 2019-10-18
A Decision Support System (DSS) for the iTab Company
Added on 2019-10-18
ShareRelated Documents
A Decision Support System (DSS) for the iTab Company You have been hired by management of the iTab Company to help determine a production plan for the next year. After studying the information you have collected (see Attachment 1), you decide 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 must meet all the policy constraints as listed in Attachment 1 (e.g., hiring, firing, overtime, minimum workforce, demand, and minimum inventory). You have been given a minimumgross 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, of the existing workforce. For this purpose, you will develop a production plan (Plan B) that lets 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 feels necessary in any month (all other constraints still hold, including the minimum workforce requirement). Management of iTab would like to see how a more flexible hiring/firing policy would influence profitability. Not knowing the precise impact of this policy 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 course Moodlesite (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 be accepted 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 the Assumptions Section. The group that manages to provide the highest total profit for the year with Plan A will receive a 10-point bonus. The runner-up will receive a 5-point bonus. Under no circumstances should 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 the Assumptions Section, Show your best results with wage rate now at $14/hour and firing cost at $800 per worker, but without 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 just Page 1 of 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 highest total profit for the year with Plan B will receive a 5-point bonus. The runner-up will receive a 3-point bonus. 3.A printout of a cover memo (max. 2 pages, single-spaced) addressed to the CEO of iTab Computers. In the memo, compare the two production plans you developed and make a recommendation. Your memo should make references to your worksheets, and it should extract some key figures from your worksheets to support your argument. Please make sure that you use the correct format for a memo, which is different from that of a letter. 4.Download and print Project 2Grading Sheet. Fill out the names. Staple your hardcopies in the 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 Company The iTab Company is a small manufacturer of tablet computers. In reviewing iTab’s financial records, management has discovered that the company’s production plan has been very inefficient in the past. This resulted in either lost sales due to insufficient inventory or excessive inventory carrying costs due to overstocking. It appears that the company needs to develop a production plan based on expected demand for its products, and on regular and overtime workforce levels. 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 Christmas holiday 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: Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec Total 1,900 1,700 1,500 1,300 1,100 1,200 1,600 1,900 1,800 1,500 2,100 2,500 20,100 The 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 regular work hours. For overtime, the same productivity is assumed, subject to a maximum of 15 percent overtime constraint. For example, if there are 20 workers on payroll in a given month, total overtime hours (Max OT hours) cannot exceed 540 hours (20×180×15%). Page 2 of Project 1
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
A-CAT Corp.: Forecasting – Ivey Case Analysislg...
|2
|628
|420