mangament homework and excel

Homework 2 PART 1. Below is the sales data for Forever Young Boutique between March of Year 1 and July of Year 5. (You can find the Excel sheet containing this data on Blackboard.)

Month Year 1 Year 2 Year 3 Year 4 Year 5 January 6381.07 11016.55 11965.68 10580.74 February 4405.83 8264.14 12761.98 13420.87 March 4566.4 9126.95 6843.95 18126.7 34476.27 April 6941.89 10970.45 20109.68 19092.9 19108.79 May 7047.23 10189.28 11148.64 15384.01 18109.54 June 6784.37 7043.85 8365.4 12237 13743.6 July 5814.23 6717.37 9469.08 13252.53 18339.25 August 6906.38 9139.72 10221 13819.77 September 8075.13 11315.19 15161.41 21755.64 October 7913.56 10838.4 17731.08 24838.1 November 9054.18 11150.94 17508.99 15955.6 December 14114.58 17592.2 27566.89 19370.7

1) Develop a forecast for the sales in August of Year 5 by using three different forecasting techniques: Three-month simple moving average, Weighted moving average with weights 0.5, 0.3, 0.2 (the weight 0.5 is given to the most recent observation), and Exponential smoothing method with a smoothing constant of 0.3. (15 points) 2) Which forecasting method you considered in Question 1 is the most accurate according to the Mean Absolute Deviation (MAD) criterion? Please justify your answer. (5 points) 3) Calculate a 95% confidence interval of the forecast made for August of the fifth year. While developing the confidence interval, use the forecasting method identified in question 2 as the most accurate one. (5 points) 4) Do you see any major peculiarities in the sales data that were not captured by the forecasting methods in Question 1? (5 points) PART 2. 5) What strategies do supermarkets, airlines, hospitals, banks, and cereal manufacturers use to influence demand? Please provide ONE strategy for each and explain that strategy in 1-2 sentences. (15 points)

6) Schedule the following activities using CPM:

Activity Immediate Predecessor Time (Weeks) A - 1 B A 4 C A 3 D B 2 E C, D 5 F D 2 G F 2 H E, G 3

a) Draw the network (5 points) b) What is the critical path? (5 points) c) How many weeks will it take to complete the project? (5 points) d) Which activities have slack, and how much? (5 points)

7) NORAN Inc. is a fabrication and assembly shop that produces radios, each of which consists of two identical electronic receiver boards and an aluminum chassis. These components are put together in radio assembly. The electronic boards are obtained from a supplier who has a one- week delivery lead-time. The aluminum chassis is fabricated internally and the fabrication lead- time is one week. The lead-time for assembling the radio is also one week. Only one shipment of completed radios is made in a week, at the beginning of the week.

Radio  Assembly  

Lead  Time:  1  week  

Electronic Boards

Lead  Time:  1  week  

Used:  2  per  radio  

Chassis

Lead  Time:  1  week  

Used:  1  per  radio  

It is the beginning of week 1. NORAN is planning fabrication and purchasing based on the master schedule shown below. (The only relevant weeks are those numbered 4, 5, 6 and 7.) The master schedule requires 50 radios to be shipped at the beginning of week 4, another 50 to be shipped at the beginning of week 5, and so forth. There are currently 10 electronic boards, and no chassis units or assembled radios, in inventory.

a) Using MRP logic, determine the schedule of planned order releases for radio assembly, electronic board purchase, and chassis fabrication that is required to meet the radio shipping master schedule. (20 points)

Radio Shipping Master Schedule

Week 1 2 3 4 5 6 7

Radio Shipment 0 0 0 50 50 100 80

Radio Assembly Requirements

Week 1 2 3 4 5 6 7

Requirements

Initial Inventory

Scheduled Receipts

Planned Order Release

Electronic Board Requirements

Week 1 2 3 4 5 6 7

Requirements

Initial Inventory

Scheduled Receipts

Planned Order Release

Chassis Fabrication Requirements

Week 1 2 3 4 5 6 7

Requirements

Initial Inventory

Scheduled Receipts

Planned Order Release

b) NORAN employees work 40 hours per week. Currently a single worker performs both chassis fabrication and radio assembly. Fabrication of each chassis takes 20 minutes, and assembly of a radio also takes 20 minutes. Show that under the plan you generated in part (a), the capacity of the worker is exceeded in at least one week. Furthermore, show how the chassis fabrication plan devised in part (a) can be modified so as to meet the master schedule requirements without exceeding the worker's capacity, and without changing the plans for electronic board purchase or radio assembly. (15 points)

Chassis Fabrication Requirements

Week 1 2 3 4 5 6 7

Requirements

Initial Inventory

Scheduled Receipts

Planned Order Release

Assembly Ops from part (a)