Question

West Chester University

ACC 303 - Spring 2018

Master Budget Problem

Ginger LLC makes two products identified as G1 and G2. Selected data for 2018 follow:

Requirements for each finished product (Raw Materials & Labor):	G1	G2
H1 (pounds)	12	10
H2 (pounds)	0	2
H3 (pounds)	2	1
Direct Labor (hours)	2	3

Other Product Information:	G1	G2
Sales price ($)	$155	$225
Sales (units)	11,500	9,500
Estimated beginning inventory (units)	400	150
Desired ending inventory (units)	300	200

	H1	H2	H3
Cost per pound	$2.00	$2.50	$0.50
Estimated beginning inventory (pounds)	3,000	1,500	1,000
Desired ending inventory (pounds)	4,000	1,000	1,500
The average wage rate for 2018 is expected to be:	$25	per hour
The effective income tax rate for the company is:	40%

Ginger uses direct labor-hours to apply overhead. Each year the company determines the overhead application rate for the year based on the budgeted output for the year. The company maintains negligible work in process inventory and expects the cost per unit for both beginnning and ending finished product inventories to be identical.

	West Chester University
	ACC 303 - Spring 2018
	Master Budget Problem
		Factory
		Overhead
		Information
Indirect materials - variable	$10,000
Misc. supplies and tools - variable	$5,000
Indirect labor - variable	$40,000
Supervision - fixed	$120,000
P/R taxes and fringe benefits - variable	$250,000
Maintenance costs - fixed	$20,000
Maintenance costs - variable	$10,080
Depreciation - fixed	$71,330
Heat, light & power - fixed	$43,420
Heat, light & power - variable		$11,000
Total	$580,830
		SGA
		Expense
		Information
Advertising	$60,000
Sales salaries	$200,000
Travel & entertainment	$60,000
Depreciation - warehouse	$5,000
Office salaries	$60,000
Executive salaries	$250,000
Supplies	$4,000
Depreciation - office	$6,000
Total	$645,000

West Chester University

ACC 303 - Spring 2018

Master Budget Problem

Other Information:

All sales are made on credit. The credit sales collection pattern is as follows:

Percent collected in month of sale	60%
Percent collected in month following sale	40%
December 2017 credit sales were:	$290,000
December 2018 credit sales were:	$360,000

All raw material purchases are made on account. The payment pattern is as follows:

Percent paid in month of purchase	25%
Percent collected in month following purchase	75%
December 2017 raw material purchases were:	$46,000
December 2018 raw material purchases were:	$42,000

The company pays direct labor, factory overheads and selling, general & administrative expenses in the periods incurred.

Forecasted income taxes are presumed to be paid in December of each year.

Company policy requires that a minimum cash balance of $50,000 be maintained at all times. Repayments of the company line of credit ar made in $10,000 increments. The company owed $750,000 on the line of credit at December 31, 2017.

The cash balance at December 31, 2017 was $50,000

The company plans to purchase new equipment in 2018 costing

$200,000

Required: Prepare and Excel spreadsheet that contains the following schedules por statement for 2018.

Use a separate tab (worksheet) for each schedule.

1. Sales Budget

2. Production budget

3. Direct materials purchases budget (units & dollars)

4. Direct labor budget

5. Factory overhead budget

6. Cost of goods sold & ending finished goods inventory budgets

7. Selling and administrative budget

8. Budgeted income statement

9. Cash budget

Answer 1