2. Based on the data above, please calculate the mean of the monthly demand of year Y1 - Y5.(use excel)

3. Based on the data above, assuming the monthly demand is proportionally seasonal the same as year Y5, show the monthly demand of years Y1, Y2, Y3, Y4. (use excel spreadsheet)

4. You run the plant for the owner that converts raw material to finished saleable umbrellas (you build excel formulas);

Please forecast the monthly raw material ordering pattern in an excel spreadsheet for year Y5 assuming starting inventory of 50 parts if all orders can only be in multiples of 10; where:

The plant makes 10% scrap during manufacturing

The business experiences 10% rework during manufacturing (rework takes 1 month to be available for sale),

The business experiences 10% returns from the customer that are resalable (returns take 2 months to be resalable),

The business experiences 10% return from customers that must be scrapped immediately,

Assumptions:

Assume you want to minimize inventory each month but it cannot be negative.

Assume the return policy was initiated by a new owner in January Y5.

5. You have a monthly line of credit with the local bank. Please forecast the maximum line of credit you will need available, and what month that will be if:

Sale price per unit is $28 /unit and is immediately available for your use (cash)

Inventory carrying cost is 25% of average 12 month forecasted inventory worth, charged monthly.

The Raw material is $10 /unit

The profit on umbrella sales is $5/unit (the owner takes that cash out of the business each month).

The plants conversion cost is $3.85/unit (includes your salary, your workers salaries, healthcare, vacation, plant heat/air, plant electric, plant water/sewer, taxes, insur. and other miscellaneous manufacturing cost etc.)

The plants scrap & return scrap cost is $15/unit (Raw + conversion)

The manufacturing rework cost is $2/unit

The business return and rework cost is $10/unit (you pay customers shipping)

Sales returns are immediately refunded full sales price

Cost of rework is paid the month it comes out of the process (workers pre-paid monthly)

Bank Loans are immediately payable when excess cash exist (no interest rate being charged)

6. What month will you have the most money tied up in inventory?

7. Would you want this business based on its ROI (Return / Investment)? Income, Losses, Investment

8. What are four ways you could improve this business if sale price cannot be raised?