Master Budget through simulation

Here's the assumptions we decided to use for the budget:
- Your profit goal is $800,000.
- We'll plan on 500,000 units, a 4.3% increase over the 479,500 we are estimating for full year 2000.
- We'll go for a $1 price increase in April to $26, bigger than our normal 2-3%.
- We decided to give a bigger discount during our normal early bird sale ($21/unit) to shift some sales out of Q2 to alleviate our production bottleneck and to give price sensitive customers the chance to buy before the price increase. I called the EPA and there's no hope of getting our permit increased beyond 150,000 units per quarter even though our plant can handle up to 300,000 units per quarter. I sure don't want the $100,000 fine for exceeding our permit. Hopefully the Early Bird sale will help the Q2 bulge. I'm also going to talk the engineers and see if we can run the plant a levels below 100,000 units. I hate to keep running all Q4, but if shut down for 2 or 3 weeks, it costs us $250,000 to shut-down and start-up again. I guess it's okay since we need the inventory because the EPA limit holds us back in Q1 and Q2.
- Our water contract with the city doesn't expire until 2003, so water will stay at $0.04, but chlorine will probably go up $2 to $31 in April. I reviewed the jug bids when I got back to my desk; it looks like $1.05 is the best price we can get when our current contract runs out in March.
- We're all set on labor; the union contract calls for $16.00 this year. I don't think we can try to negotiate any concessions.
- Given all the above, we're probably going to have to keep Fixed Mfg OH and G&A) flat or look for some cuts to hit our profit goal. Plus, equipment depreciation will go up from $150,000 per qtr to $160,000 when the new filler is installed in July. G&A Depr will stay steady at $55,000 per quarter. We'll have to keep Var MOH at $3 as well.
- For now, I'll assume commissions stay at 3%.
- You are going ahead with the $400,000 investment in the new filler in July. We'll probably be able to borrow 75% from the bank.
- I'm worried about our borrowing capacity. Our customer have been paying in 45 days. We have normal 30 day terms the City for water and the jug supplier, but we're still on COD with Chlorine - which really hurts cash flow.
- Per your request, the Executive Summary will include my comments as to which parts of the budget have the most risk; which parts we are most likely to fall short of goal.
I'll send you an email if I need any other information to do the budget.
We'll use the same format as last year, I've already got Steve setting it up for me.

See attached file for full problem description.