Budget project

Create a budget for a year for a company. Please use a separate tab for each segment (sales, inventory, direct material, direct labor, overhead and cash).

Sales

begins with 5,000 units and increases2% per month

the sale price at the beginning of the year is $28 per unit

on May 1 we intend to raise our price per unit to $29

on October 1 we intend to raise our price per unit to $30

15% of sales are cash

Inventory

begins with 1,150 units

Maintain an ending balance each month equal to 25% of next month production

Direct material

2 pounds of raw material needed for each unit of production

Begin with raw materials of 3,000 pounds

End each month with 30% of next month needed material

Cost per pound starts the year at $1.25

On July 1 we expect a price increase of $ 0.20 per pound

Direct labor

Each unit produced uses 1 hour of type one labor and .5 hours of type two labor

Type one labor is paid $12 per hour

Type two labor is paid $20 per hour

Overhead

Indirect material is 5% of production (units)

Repairs and maintenance is 2% of production (units)

Sales commissions are 3% of sales (dollars)

Depreciation is $500 per month

Fixed labor (overhead) is $800 per month

Insurance and taxes are $300 and $250 per month respectively

Cash

The bank charges a 2% fee on card purchases (cash purchases)

5% of credit sales are collected in the month of the purchase

85% of credit sales are collected in the month following the month of purchase

9% of credit sales will be collected in the month following

A dividend of $4,000 is paid at the end of each quarter (3/31, 6/30, 9/30 and 12/31)

Equipment of $30,000 will be purchased on May 15.

The company must maintain a minimum cash balance of $5,000 per our debt covenant. The company has a line of credit for short-term borrowing. The interest rate on the line of credit is 5%. Interest will be paid monthly on the line of credit.

Balance sheet information

Accounts receivable Sales for November was $105,000

Accounts receivable Sales for December was $110,000

Cash on December 31 was $11,000

THANK YOU!