Chapter 7: Applying Excel

Year End December 2015

Master Budget

Given Data - Assumptions

Quarter

1

2

3

4

Budgeted sales in cases

15,000

20,000

35,000

40,000

Sales Budget

Selling price per case

$20

per case

Sales collected in the quarter sales are made

70%

Sales collected in the quarter after sales are made

30%

Production Budget

Desired ending finished goods inventory at quarter end is

20%

of the budgeted unit sales of the next quarter

Q4 desired units in ending finished goods inventory is assumed to be

3,000

Direct Materials Budget

Raw materials required to produce one case

15

pounds

Cost per pound of raw material

0.20

Percentage of net quarter's production needs in ending inventory

10%

Q4 production needs in ending inventory is assumed to be

22,500

Percentage of purchases paid in the quarter purchased

50%

Percentage of purchases paid in the quarter after purchased

50%

Enter a formula into each of the cells coded grey.

Link as much to the Data/Assumptions Box as Possible.

Prepare the Sales Budget-Schedule 1

Quarter

Year

1

2

3

4

1

Budgeted case sales

Selling price per case

Total sales

Prepare the Schedule of Expected Cash Collections

Quarter

1

2

3

4

Year

Accounts receivable, beginning balance

First-quarter sales

Second-quarter sales

Third-quarter sales

Fourth-quarter sales

Total cash collections

Prepare the Production Budget-Schedule 2

Quarter

1

2

3

4

Year

Total number of budgeted case sales (Schedule 1)

Add desired finished goods inventory at quarter end

Total needs

Less beginning inventory (# of cases)

Required production in cases

Prepare the Raw (direct) Materials Purchases Budget

Quarter

Schedule 3

1

2

3

4

Year

Required production - number of cases - Schedule 2

Raw materials in lbs. required to produce one case

Total production needs (pounds)

Add desired ending inventory of raw materials (pounds)

Total needs (pounds)

Less beginning inventory of raw materials (pounds)

Raw materials to be purchased (pounds)

Cost of raw materials per pound

Total cost of raw materials to be purchased

Prepare the Schedule of Expected Cash Payments

Quarter

1

2

3

4

Year

Accounts payable, beginning balance

Cash paid in the first-quarter for purchases

Cash paid in the second-quarter for purchases

Cash paid in the third-quarter for purchases

Cash paid in the fourth-quarter for purchases

Total cash disbursements

Prepare the Direct Labor Budget-Schedule 4

Quarter

Given:

1

2

3

4

Year

Each case requires

0.40

DLH

Required production in cases - Schedule 2

Each worker is paid

$ 15.00

per hour

Direct labor-hours per case

Total direct labor-hours needed

Direct labor cost per hour

Total direct labor cost

Prepare a Manufacturing Overhead Budget-Schedule 5

Quarter

Given:

1

2

3

4

Year

Variable manufacturing overhead rate

$ 4.54

DLH

Budgeted direct labor-hours (Schedule 4)

(b)

Fixed manufacturing overhead is

$ 60,600

per Q

Variable manufacturing overhead rate per hour

Depreciation

$ 15,000

per Q

Total variable manufacturing overhead

Fixed manufacturing overhead

Total manufacturing overhead

(a)

Less depreciation - non cash expense

Cash disbursements to manufacturing overhead

Total manufacturing overhead (a)

Budgeted direct labor-hours (b)

Predetermined overhead rate for the year (a) / (b)

Prepare an Ending Finished Goods Inventory Budget-Schedule 6

Quantity

Cost

Total

Item

Production cost per case

Direct materials

15.00

pounds

0.20

per pound

Direct labor

0.40

hours

15.00

per hour

Manufacturing overhead

0.40

hours

10.00

per hour

Unit product cost

Budgeted finished goods inventory:

Ending finished goods inventory in cases (Schedule 2)

Unit product cost (see above)

Ending finished goods inventory in dollars

Prepare a Selling and Administrative Budget-Schedule 7

Quarter

Given:

1

2

3

4

Year

Variable selling and administrative expense per case

$ 1.80

Budgeted number of case sales (Schedule 1)

Fixed selling and administrative:

Variable selling and administrative expense per case

Advertising

$ 18,000

per Q

Variable selling and administrative expense

Executive salaries

$ 40,000

per Q

Fixed selling and administrative expenses:

Depreciation

$ 6,000

per Q

Advertising

Insurance

$ 9,500

per Q

Executive salaries

Property taxes

$ 3,500

per Q

Insurance

Property taxes

Depreciation

Total fixed selling and administrative expenses

Total selling and administrative expenses

Less depreciation

Cash disbursements for selling and administrative expenses

Prepare a Cash Budget-Schedule 8

Quarter

Given:

1

2

3

4

Year

A minimum of $20,000 cash is required in the bank

Beginning cash balance

The company may borrow any amount at the beginning of any quarter

Add cash receipts:

in increments of $10,000

Collections from customers - from Schedule 1

Annual simple interest rate

5%

Total cash available

Loans must be repaid at year end including all interest incurred

Less cash disbursements:

Dividends paid

$ 8,000

Q1-Q4

Direct materials - from Schedule 3

Equipment purchases

$ 50,000

Q1

Direct labor - from Schedule 4

$ 30,000

Q2

Manufacturing overhead - from Schedule 5

$ 20,000

Q3

Selling and administrative- from Schedule 7

$ 20,000

Q4

Equipment purchases

Dividends

Total cash disbursements

Excess (deficiency) of cash available over disbursements

Financing:

Borrowings (at the beginning of quarters)

Repayments (at end of the year)

Interest expense (will calculate for you)

-

Total financing

Ending cash balance (also next Q beginning balance)

Prepare a Budgeted Income Statement -Schedule 9

Sales - from Schedule 1

Cost of goods sold - from Schedules 1 & 6

Gross margin

Selling and administrative expenses - from Schedule 7

Net operating income

Interest expense - from Schedule 8

Net income

Prepare a Budgeted Balance Sheet -Schedule 10

Assets

Current assets:

Cash - from Schedule 8

Accounts receivable - from Schedule 1

Raw materials inventory- from Schedule 3

Finished goods inventory - from Schedule 6

Total current assets

Plant, property, and equipment:

Land

Buildings and equipment (BS + Schedule 8)

Accumulated depreciation (BS + Sched 5 + Sched 7)

(292,000)

will calculate for you; will include prior year accumulated depreciation ending balance per the balance sheet plus current year depreciation expense

Property, plant, and equipment, net

Total assets

Liabilities and Stockholders'' Equity

Current liabilities:

Accounts payable (raw materials) - from Schedule 3

Stockholders' equity:

Common stock, no par (PY Balance sheet)

Retained earnings (PY balance sheet, CY income statement, Schedule 8)

Total stockholders' equity

Total liabilities and stockholders' equity