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. Factory overhead budget

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

3. Selling and administrative budget

4. Budgeted income statement

5. Cash budget

Answer 1

Statement Showing Sales Budget					Manufacturing Overhead Budget
	G1	G2			Variable Overhead
Sales ( in units)	11500	9500			Indirect Material	$10,000
Sales ($/Unit)	$155	$225			Misc. Supplies & Tools	$5,000
Sales Revenue	$1,782,500	$2,137,500			Indirect labour	$40,000
					P/R Taxes and Frienge Benefit	$250,000
Statement Showing Production Budget					Maintainance Cost	$10,080
	Box C	Box P			Heat Light& power	$11,000
Sales (Units)	11500	9500			Fixed Overhead
Add: Ending inventory	300	200			Supervision	$120,000
Less: Opening Inventory	-400	-150			Maintainance Cost	$20,000
Production required	11400	9550			Depreciation	$71,330
					Heat Light& power	$43,420
Statement Showing Direct Labour Budget					Total Manufacturing Overhead Budget	$580,830
	G1	G2
Prod Req. (in Unit)	11400	9550			Selling & Administrative Expense Budget
Direct Labour Hour/Unit	2.00	3.00			Advertising	$60,000
Total Direct Labour Hour	22800	28650			Sales Salaries	$200,000
Direct Labour Rate	$25	$25			Travel & Entertainment	$60,000
Total Direct Labour Cost	$570,000	$716,250			Depreciation Warehouse	$5,000
					Office Salaries	$60,000
Statement Showing Raw material Purchase Budget					Executive Salaries	$250,000
	H1	H2	H3		Supplies	$4,000
Required For G1 ( Pound /Unit)	12 Pound		2 Pound		Depreciation Office	$6,000
RM Required for G1 Production 11400Unit ( in Pounds) (a)	136800		22800		Total	$645,000
Required For G2 ( Pound /Unit)	10 Pound	2 Pound	1 Pound
RM Required for G2 Production 9550 Unit ( in Pounds) (b)	95500	19100	9550		Budgeted Income Statement
Total Raw material Required ( in Pound)	232300	19100	32350		Sales Revenue from Sales Budget	$3,920,000.00
Add: Closing Inventory	4000	1000	1500		Less: Cost of Goods Sold	-2398685
Less: Opening Inventory	-3000	-1500	-1000		Gross Margin	$1,521,315.00
Purchase Quantity Required	233300	18600	32850		Less: Selling & Admin Expense	-$645,000.00
Purchase Cost /Pound	$2	$2.50	$0.50		Income Before Tax	$876,315.00
Total Purchase Cost	$466,600	$46,500	$16,425		Less: Income Tax Expense @40%	$350,526.00
					Net Income	$525,789.00
Statement Showing Sales Cost of Goods Sold & Closing Stock
	G1	G2			Cash Budget
Manufacturing Cost/Unit (a)	$97.58	$134.37			For the Month of May
Sales ( in units) (b)	11500	9500			Beginning Cash Balance	$50,000.00
Cost of Goods Sold (a*b)	$1,122,170	$1,276,515			Add: Cash Collection	$3,892,000.00
Closing Stock Quantity (in Unit) ( c)	300	200			Total Cash Available (a)	$3,942,000.00
Value of Closing Stock(a*c)	$29,274	$26,874			Less:Cash Disbursement
					For Merchandise	$532,585.00
W/Note: Computation of Manufacturing Cost/Unit					For Selling and Administrative Expense	$634,000.00
	G1	G2			For Manufacturing Overhead	$509,500.00
Direct Material Cost: H1(12*$2) (10*$2)	$24.00	$20.00			New Equipment Purchased	$200,000.00
H2 (2*$2.50)		$5.00			Total Cash Disbursement (b)	$1,876,085.00
H3( 2*$1) (1*$0.50)	1	0.5			Excess ( Deficiency) of Cash (a-b)	$2,065,915.00
Direct Labour ($25*2 ) ($25*3)	$50.00	$75.00
Applied Manufacturing Overhead	$22.58	$33.87			Statement showing Expected Cash Collection from Sales
($11.29*2) ($11.29*3)					Detail	Amount
Manufacturing Cost/Unit	$97.58	$134.37			Total Sale made in 2018	$3,920,000.00
					Less: Collection not made for Dec2018(360000*40%)	$144,000.00
Predertmined Overhead Recovery Rate					Total Collection for 2018 Sales	$3,776,000.00
($580830/51450Hour)=11.29					Add: Collection made for Dec2017 (290000*40%)	116000
					Total Cash Receipt	$3,892,000.00
					Statement Showing Cash Disbursement for Purchase
					Detail	Amount
					Total Purchase Made in 2018	$529,585.00
					Less: Payment not made for Dec2018(42000*75%)	$31,500.00
					Total Payment made for 2018 Purchase	$498,085.00
					Add: Payment made for Dec2017 (46000*75%)	34500
					Total Cash Payment Made	$532,585.00