Budgeting excel problems
(See attached files for full problem description)
It's that time of year again - budget time!
As you know, we're starting late, so I need you to throw this together in a week.
Yesterday, the management team met and made the following decisions for the budget :
- Let's shoot for around a 5% increase in sales (155,000 bags?) with a 2.5% price increase (go from $3.00 to $3.08 on July 1). They're forecasting an early spring, so sales will probably pick-up early this year.
- Assume our expense will go up around 2.5-3% except for wages. Recall they agreed to a lower raise ($.25 to $7.00 on July 1) in exchange for a guarantee of 2500 hours per quarter. Be sure to not to schedule production below 31,250 bags (at .08 hours per bag, we need 31,250 to meet the Union contract).
- Since most of G&A and fixed overhead is fixed, let's only add 1-2% there.
- For now, keep the commissions at 5%.
- Let's assume we can keep overhead at $2 per hour.
- Customers continue to pay us slower and slower; let's assume 60 days (yuk!), but we'll have to keep paying our suppliers in 30 days (or they'll stop shipping to us!).
- We're hoping raw materials stay steady at $.0275 per pound, but we know the bags are going from $.47 to $.50 on July 1.
- We're also going to buy a second company car in October. Budget $25,000. That will probably double our G&A depreciation expense from $1500 to $3000 per quarter. Factory Depr should stay at $5000 per quarter.
Send me an email if you need any other data.
From what we can see, 2001 should be about the same as 2000 - another okay year.
I'll have Steve email you the budgeting template; he'll enter any data he already knows.
The solution explains how to prepare an annual budget given the various assumptions that have been made for Plymouth Company