Problem 6-5A Managerial Accounting: Budgeting
calliewatsonTheCorrectPR6-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 | ||