West Chester University ACC 303 - Spring 2018 Master Budget Problem Ginger LLC m
ID: 2551079 • Letter: W
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
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
Explanation / Answer
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
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.