In this assignment you will prepare a series of budgets (Sales Budget, Cash Collections Budget, Production Budget, Direct Materials Budget, Direct Labor Budget, Manufacturing Overhead Budget, SG&A Budget, and Cash Budget) which you will then use to prepare a Budgeted 2016 Income Statement. You may find it helpful to prepare a COGS worksheet in addition to the aforementioned budgets as this will make it easier to complete the budgeted Income Statement. All work must be done in Excel using cell referencing and formulas.

Budget Assumptions:

Unit Sales:

Month Units Sold

Oct 2015 1,298

Nov 2015 1,371

Dec 2015 2,200

Jan 1,000

Feb 750

Mar 1,500

Apr 2,000

May 2,500

Jun 3,000

Jul 2,750

Aug 2,700

Sep 2,000

Oct 1,500

Nov 1,750

Dec 2,500

Jan 2017 1,100

Feb 2017 800

Average Selling Price: $99.95 per unit

Customer Collections:

12.5% collected the month of sale

53.75% collected the month after the sale

19.25% collected 2 months after the sale

13.5% collected 3 months after the sale

1% never collected

Ending Finished Goods Inventory should be 35% of next months sales (rounded up to next whole unit)

Ending Direct Materials Inventory should be 50% of next months needs (rounded up to the next pound)

Cost per pound for Direct Material is $1.275

Amount of Direct Material needed per unit is 2.35 pounds

Direct Material is purchased in 100 pound increments

Payments for Direct Materials are made the month after purchases are made.

Direct Labor costs are $22.50 per hour

Direct Labor hours required per unit are 1.95 hours per unit

Variable Manufacturing Overhead rate is $1.74 per Direct Labor Hour Manufacturing Overhead Allocation Rate is $3.75 per Direct Labor Hour Depreciation included in Manufacturing Overhead is $2,650 per month Fixed Manufacturing Overhead is $6,987 per month and includes all fixed manufacturing overhead amounts except:

Taxes on Manufacturing Facility: $4,675 per year with onehalf paid in April and the balance in November

Insurance on Manufacturing Facility: $5,500 per year with equal quarterly payments in March, June, September, and December

All Manufacturing Overhead expenses are paid in the month they are incurred

Selling, General and Administrative Expenses:

Variable SG&A Expenses are $2.00 per unit

Fixed SG&A Expenses:

Salaries: $12,000 per month

Office Expenses: $250 per month

Advertising: $300 per month

Rent: $2,000 per month

Depreciation: $450 per month

Utilities: $275 per month

Other Expenses: $175 per month

All SG&A Expenses are paid in the month they are incurred

Ending Cash Balance on December 31, 2015 was $41,325

Company has unlimited Line of Credit at 5% annually

Interest is paid monthly

Company borrows and repays in $10,000 increments

Balance outstanding on Line of Credit as of 12/31/2015 was $350,000

Desired ending cash balance at the end of each month is $40,000

The Budgeted Income Statement should follow the following format:

Budgeted Income Statement

For Calendar Year 2016

Sales $ xxx,xxx

Cost of Goods Sold xxx,xxx

Gross Profit $ xxx,xxx

Salaries $ xxx,xxx

Office Expenses xxx,xxx

Advertising xxx,xxx

Rent xxx,xxx

Depreciation xxx,xxx

Utilities xxx,xxx

Bad Debt Expense xxx,xxx

Other Expenses xxx,xxx

Total Expenses $ xxx,xxx

Net Operating Income $ xxx,xxx

Interest Expense xxx,xxx

Net Income $ xxx,xxx