I am so confused. Can somebody look at this?
- Thread starter phoward
- Start date
Here is the other thing...I think I see where the confusion lies. The average that you have provided is the average demand (not production) per month for the next year. Unfortunately, the company can only produce 500 units per month. This means that at an average, the company is short 183 units per month. I am so confused.
This is what I have so far. Just building the table so I can pivot for these questions:
Provide a detailed cost breakdown for using level vs a chase strategy to meet the increased demand
Which strategy do you recommend?
How much savings would result from the plan you recommend?
I can answer these questions as long as my data and formulas are set up.
Provide a detailed cost breakdown for using level vs a chase strategy to meet the increased demand
Which strategy do you recommend?
How much savings would result from the plan you recommend?
I can answer these questions as long as my data and formulas are set up.
I would add a column for stock levels
Then work out the shortage per month
and that tells you how much extra production you need
You say each employee can produce an extra 20 units per month
What you are missing is how many employees there are?
That will determine if your employees can make up the shortfall or not
From that you can decide if you need to use your employees of outsource the production difference
Then work out the shortage per month
and that tells you how much extra production you need
You say each employee can produce an extra 20 units per month
What you are missing is how many employees there are?
That will determine if your employees can make up the shortfall or not
From that you can decide if you need to use your employees of outsource the production difference
p45cal
Well-Known Member
There's a shortfall in production.
Current capacity of 6000 pa. Demand of 8200 pa. Very roughly, a shortfall 2200 pa.
You say: Each employee added can produce an additional 20 units per month and is paid $1000 per month.
Which is the same as saying each additional employee can produce an additional 12x20 units pa. (=240).
So you could cover that 2200 shortfall by employing an additional 9.166 employees.
You try employing .166 of an employee! Actually it's possible with timing, BUT you also say: Changes in production level cost $100 per unit due to hiring, line changeover costs, and so forth. This is an astonishing figure, and a bit woolly. To what units do you add the $100? All of them?! Just those produced in one month at changeover? Whatever, it looks very expensive. The message being: avoid changes in production level.
In the attached there's a cell B16 where you can plug in the additional employees and some dead simple formulae so you can see inventory levels over the year:
Here I've used 9. Interestingly, the remaining inventory at the end of the year is only 40 less than it was at the beginning of the year (if you plugged in 9.1666 you'd get the same inventory at both those points, of course). It's also interesting that the minimum inventory is 80 which is about 10% of max monthly demand - which might be considered a safe level to cover unexpected demand.
Now if you plug in smaller numbers (in cell B16, you'll start seeing shortfalls in some months. I'll leave it to you to do some what-if calculations to see the costs of making up those shortfalls with overtime and/or subcontracting.
Here it is with 8 additional employees:
You could cover shortfalls with additional overtime if you knew that the higher demand in September/October was seasonal; you can get 10% of 660 (=66) more units per month on overtime alone, so if you were to get max overtime in Jul/Aug/Sept/Oct you might cover the shortfalls in Oct/Nov without any other method of increased output.
Overtime looks like it might be more expensive than subcontracting since overtime costs an extra $25 per unit where subcontracting is only $15 extra per unit.
Current capacity of 6000 pa. Demand of 8200 pa. Very roughly, a shortfall 2200 pa.
You say: Each employee added can produce an additional 20 units per month and is paid $1000 per month.
Which is the same as saying each additional employee can produce an additional 12x20 units pa. (=240).
So you could cover that 2200 shortfall by employing an additional 9.166 employees.
You try employing .166 of an employee! Actually it's possible with timing, BUT you also say: Changes in production level cost $100 per unit due to hiring, line changeover costs, and so forth. This is an astonishing figure, and a bit woolly. To what units do you add the $100? All of them?! Just those produced in one month at changeover? Whatever, it looks very expensive. The message being: avoid changes in production level.
In the attached there's a cell B16 where you can plug in the additional employees and some dead simple formulae so you can see inventory levels over the year:
Here I've used 9. Interestingly, the remaining inventory at the end of the year is only 40 less than it was at the beginning of the year (if you plugged in 9.1666 you'd get the same inventory at both those points, of course). It's also interesting that the minimum inventory is 80 which is about 10% of max monthly demand - which might be considered a safe level to cover unexpected demand.
Now if you plug in smaller numbers (in cell B16, you'll start seeing shortfalls in some months. I'll leave it to you to do some what-if calculations to see the costs of making up those shortfalls with overtime and/or subcontracting.
Here it is with 8 additional employees:
You could cover shortfalls with additional overtime if you knew that the higher demand in September/October was seasonal; you can get 10% of 660 (=66) more units per month on overtime alone, so if you were to get max overtime in Jul/Aug/Sept/Oct you might cover the shortfalls in Oct/Nov without any other method of increased output.
Overtime looks like it might be more expensive than subcontracting since overtime costs an extra $25 per unit where subcontracting is only $15 extra per unit.
Last edited: