Problem 6-5A Managerial Accounting: Budgeting

TheCorrectPR6-5.xlsx

Home >Business & Finance homework help >Accounting homework help >Problem 6-5A Managerial Accounting: Budgeting

Pr. 21(6)-5A

Problem 21(6)-5A
Name:	0
Section:	# N-box Incorrects due to blanks COUNTIF(B15:AT24," ")
66
Score:	0%	# N-box +B-box corrects COUNTIF(B15:AT24," ")
0
Key Code:	[Key code here]	Total SUM(AD13:AD15)
Instructions	66
Answers are entered in the cells with gray backgrounds.	Percentage =(AD16-AD13-AD14)/AD16
Cells with non-gray backgrounds are protected and cannot be edited.	0%
An asterisk (*) will appear to the right of an incorrect entry.	Notes:
If number-entry box is blank (this would be an incorrect answer for N-boxes), error check returns two spaces, " "
If number-entry or blank-entry box is incorrect, returns "*"
1.	If number-entry or blank-entry box is correct, returns single space, " "
REGINA SOAP CO.	Accounts Receivable	Use data verification to set data entry to whole number >= 0, and use drop-downs for lables and names, so that students can't enter a space in a box and have it counted as correct.
Budgeted Income Statement	Accounts Payable	Conditional formatting might be used but wasn't here, to hide some of the error check return symbols. If A1 = "~*", then font = red, if something else, then font = background color.
For the Year Ending December 31, 2017
Sales	Mark Sears: Enter as a formula of units x price.
Cost of goods sold:	Materials inventory, May 1, 2012
Direct materials	Mark Sears: Enter a formula of units x unit cost.
Direct labor
Factory overhead	Mark Sears: Complete the supporting calculations below and enter appropriate amount here.		Cost of goods manufactured
Cost of goods sold		Cost of materials available for use
Gross profit		Miscellaneous cost
Operating expenses:	Property taxes
Selling expenses:	Supplies
Sales salaries and commissions	Mark Sears: Complete the supporting calculations below and enter the appropriate amount here.
Advertising
Miscellaneous selling expense	Mark Sears: Complete the supporting calculations below and enter the appropriate amount here.
Total selling expenses
Administrative expenses:	Finished goods inventory, May 1, 2012
Office and officers salaries	Mark Sears: Complete the supporting calculations below and enter the appropriate amount here.
Supplies	Mark Sears: Complete the supporting calculations below and enter the appropriate amount here.
Miscellaneous admin. expenses	Mark Sears: Complete the supporting calculations below and enter the appropriate amount here.		Finished goods inventory, May 31, 2012
Total administrative expenses		Materials inventory, May 1, 2012
Total operating expenses
Income before income tax
Income tax expense
Net income
Supporting calculations for budgeted income statement:
Factory overhead:	Office and officers salaries:
Variable overhead	Mark Sears: Enter a formula of units x variable unit cost.		Fixed portion
Depreciation		Variable portion	Mark Sears: Enter a formula of units x variable unit cost.
Other		Total
Total
Sales salaries and commissions:	Supplies expense:
Fixed portion		Fixed portion
Variable portion	Mark Sears: Enter a formula of units x variable unit cost.		Variable portion	Mark Sears: Enter a formula of units x variable unit cost.
Total		Total
Miscellaneous selling expenses:	Miscellaneous administrative expenses:
Fixed portion		Fixed portion
Variable portion	Mark Sears: Enter a formula of units x variable unit cost.		Variable portion	Mark Sears: Enter a formula of units x variable unit cost.
Total		Total
2.
REGINA SOAP CO.
Budgeted Balance Sheet
For the Year Ending December 31, 2017
Assets
Current assets:
Cash	Mark Sears: Complete the supporting calculations below and enter the appropriate amount here.
Accounts receivable
Inventories:
Finished goods
Work in process
Materials
Prepaid expenses
Total current assets
Property, plant, and equipment:
Plant and equipment	Mark Sears: Enter a formula of current plant and equipment plus expected acquisitions.
Less accumulated depreciation	Mark Sears: Enter a formula of current balance plus expected 2013 depreciation.
Total assets
Liabilities
Current liabilities:
Accounts payable
Stockholders' Equity
Common stock
Retained earnings	Mark Sears: Complete the supporting calculations below and enter the appropriate amount here.
Total stockholders' equity
Total liabilities and stockholders' equity
Supporting calculations for budgeted balance sheet:
Cash balance:
Balance, January 1, 2017
Plus cash from operations:
Net income (from budgeted income statement)
Depreciation (add back noncash item)
Less planned nonoperating cash outflows:
Dividends to be paid in 2017
Plant and equipment to be acquired in 2017
Balance, December 31, 2017
Retained earnings balance:
Balance, January 1, 2017
Plus expected net income for 2017
Less dividends to be paid in 2017
Balance, December 31, 2017

Sol

Problem 21(6)-5A
Name:	Solution
Section:
Score:	ON
Instructions
Answers are entered in the cells with gray backgrounds.
Cells with non-gray backgrounds are protected and cannot be edited.
An asterisk (*) will appear to the right of an incorrect entry.
1.
REGINA SOAP CO.
Budgeted Income Statement
For the Year Ending December 31, 2017
Sales	$ 1,000,000 Mark Sears: Enter as a formula of units x price.
Cost of goods sold:
Direct materials	$ 220,000 Mark Sears: Enter a formula of units x unit cost.
Direct labor	130,000
Factory overhead	132,000 Mark Sears: Complete the supporting calculations below and enter appropriate amount here.
Cost of goods sold	482,000
Gross profit	$ 518,000
Operating expenses:
Selling expenses:
Sales salaries and commissions	$ 136,000 Mark Sears: Complete the supporting calculations below and enter the appropriate amount here.
Advertising	64,000
Miscellaneous selling expense	56,000 Mark Sears: Complete the supporting calculations below and enter the appropriate amount here.
Total selling expenses	$ 256,000
Administrative expenses:
Office and officers salaries	$ 96,400 Mark Sears: Complete the supporting calculations below and enter the appropriate amount here.
Supplies	25,000 Mark Sears: Complete the supporting calculations below and enter the appropriate amount here.
Miscellaneous admin. expenses	14,000 Mark Sears: Complete the supporting calculations below and enter the appropriate amount here.
Total administrative expenses	135,400
Total operating expenses	391,400
Income before income tax	$ 126,600
Income tax expense	30,000
Net income	$ 96,600
Supporting calculations for budgeted income statement:
Factory overhead:	Office and officers salaries:
Variable overhead	$ 80,000 Mark Sears: Enter a formula of units and variable unit cost.	Fixed portion	$ 72,400
Depreciation	40,000	Variable portion	24,000 Mark Sears: Enter a formula of units and variable unit cost.
Other	12,000	Total	$ 96,400
Total	$ 132,000
Sales salaries and commissions:	Supplies expense:
Fixed portion	$ 46,000	Fixed portion	$ 5,000
Variable portion	90,000 Mark Sears: Enter a formula of units and variable unit cost.	Variable portion	20,000 Mark Sears: Enter a formula of units and variable unit cost.
Total	$ 136,000	Total	$ 25,000
Miscellaneous selling expenses:	Miscellaneous administrative expenses:
Fixed portion	$ 6,000	Fixed portion	$ 4,000
Variable portion	50,000 Mark Sears: Enter a formula of units and variable unit cost.	Variable portion	10,000 Mark Sears: Enter a formula of units and variable unit cost.
Total	$ 56,000	Total	$ 14,000
2.
REGINA SOAP CO.
Budgeted Balance Sheet
For the Year Ending December 31, 2017
Assets
Current assets:
Cash	$ 135,800 Mark Sears: Complete the supporting calculations below and enter the appropriate amount here.
Accounts receivable	125,600
Inventories:
Finished goods	$ 69,300
Work in process	32,500
Materials	48,900	150,700
Prepaid expenses	2,600
Total current assets	$ 414,700
Property, plant, and equipment:
Plant and equipment	$ 400,000 Mark Sears: Enter a formula of current plant and equipment plus expected acquisitions.
Less accumulated depreciation	196,200 Mark Sears: Enter a formula of current balance plus expected 2013 depreciation.	203,800
Total assets	$ 618,500
Liabilities
Current liabilities:
Accounts payable	$ 62,000
Stockholders' Equity
Common stock	$ 180,000
Retained earnings	376,500 Mark Sears: Complete the supporting calculations below and enter the appropriate amount here.
Total stockholders' equity	556,500
Total liabilities and stockholders' equity	$ 618,500
Supporting calculations for budgeted balance sheet:
Cash balance:
Balance, January 1, 2017	$ 85,000
Plus cash from operations:
Net income (from budgeted income statement)	$ 96,600
Depreciation (add back noncash item)	40,000	136,600
Less planned nonoperating cash outflows:
Dividends to be paid in 2017	$ (10,800)
Plant and equipment to be acquired in 2017	(75,000)	(85,800)
Balance, December 31, 2017	$ 135,800
Retained earnings balance:
Balance, January 1, 2017	$ 290,700
Plus expected net income for 2017	96,600
Less dividends to be paid in 2017	(10,800)
Balance, December 31, 2017	$ 376,500