Problem 6-5A Managerial Accounting: Budgeting

profilecalliewatson
TheCorrectPR6-5.xlsx

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