Question

Financial Statements using Excel's SUM Function and Cell Referencing

A set of financial statements for Great Taste Restaurants Inc. are presented with missing information. The Controller has asked you to determine the missing amounts in the Income Statement, Statement of Stockholders’ Equity, and Balance Sheet. Use the information included in the Excel Simulation and the Excel functions described below to complete the task.

• Cell Reference: Allows you to refer to data from another cell in the worksheet. From the Excel Simulation below, if in a blank cell, “=C14” was entered, the formula would output the result from cell C14, or $1,200,000 in this example.
• Basic Math functions: Allows you to use the basic math symbols to perform mathematical functions. You can use the following keys: + (plus sign to add), - (minus sign to subtract), * (asterisk sign to multiply), and / (forward slash to divide). From the Excel Simulation below, if in a blank cell “=C17+C18” was entered, the formula would add the values from those cells and output the result, or 110,000 in this example. If using the other math symbols the result would output an appropriate answer for its function.
• SUM function: Allows you to refer to multiple cells and adds all the values. You can add individual cell references or ranges to utilize this function. From the Excel Simulation below, if in a blank cell “=SUM(C17,C18,C19)” was entered, the formula would output the result of adding those three separate cells, or 192,500 in this example. Similarly, if in a blank cell “=SUM(C17:C19)” was entered, the formula would output the same result of adding those cells, except they are expressed as a range in the formula, and the result would be 192,500 in this example.

1). Income Statement

a). Solve for Total operating expenses.

b). Include sum function in your calculations to solve for wage expense.

c). Solve for the interest revenue and the net income.

5. . HOME . INSERT FILE Relationships among Financial Statements - Excel PAGE LAYOUT FORMULAS DATA REVIEW VIEW Calculate Now Function Defined Library Names Formula Auditing Calculation calculate Sheet Options Calculation C16 Great Taste Restaurants Inc. Income Statement For the year ended December 31, 2016 Sales Revenue 1,200,000 Operating Expenses: Wages Expense vnence Utilities Expense 35,000 Rent Expense 75,000 Advertising Expense 82,500 Depreciation Expense 50,000 Loss on disposal of assets 10,000 Total Operating Expenses Income from Operations 782,500 Other Items: Interest Revenue Interest Expense (15,000) Income before Income Taxes 769,500 Income tax expense 192.100 Net Income 3121 Statement of Stockholder's Fauit Sheet1 ... 6 ... READY Attempt(s) 3/3

2). Statement of Stockholder's Equity.

a). Using cell reference, show the Net Income under the retained earnings column.

b). Use a cell reference to display the the net income value you produced in letter a. under total column.

c). Solve for the missing dividends under the retained earnings column.

d). Use a cell reference to display the dividends declared value you calculated in letter c. under the total column.

e). Use a sum function to calculate the total stockholder's equity.

3). Balance sheet

a). Solve for the account receivable, accumulated depreciation and total asset values.

b). Use the sum function to calculate total current liabilities.

c). Use cell references to show values for common stock, additional paid capital, and retained earnings accounts.

d). Solve for the total liabilities and SE.

Answer 1

Prepare financial statement using spreadsheet as follows:

	B	C	D	E	F
2	Great Taste Restaurants Inc.
3	Income Statement
4	For the year ended December 31,2016
5	Sales revenue	$1,200,000
6	Operating Expenses:
7	Wages Expense	$165,000
8	Utilities Expense	$35,000
9	Rent Expense	$75,000
10	Advertising Expense	$82,500
11	Depreciation Expense	$50,000
12	Loss on Disposal of assets	$10,000
13	Total Operating Expenses	$417,500
14	Income From Operations	$782,500
15	Other item:
16	Interest Revenue	$2,000
17	Interest Expense	($15,000)
18	Income before Income taxes	$769,500
19	Income tax expense	$192,000
20	Net Income	$577,500
21
22
23	Great Taste Restaurants Inc.
24	Statement of Stockholders' Equity
25	For the year ended December 31,2016
26		Common Stock	Paid-in capital	Retained Earnings	Total
27	Beginning January 1, 2016	$50,000	$400,000	$800,000	$1,250,000
28	Stock issuance	$0	$0		$0
29	Net Income			$577,500	$577,500
30	Dividends declared			($215,000)	($215,000)
31	Balance December, 2016	$50,000	$400,000	$1,162,500	$1,612,500
32
33
34	Great Taste Restaurants Inc.
35	Balance Sheet
36	December 31, 2016
37	Assets		Laibilities
38	Current Assets		Current Liabilities:
39	Cash	$300,000	Accounts payable	$75,000
40	Short-term investments	$50,000	Unearned revenue	$45,000
41	Accounts receivable	$75,000	Short-term note payable	$92,500
42	Supplies	$120,000	Total-Current Laibilities	$212,500
43	Total Current Assets	$545,000	Notes payable	$350,000
44	Property, Plant and Equipment:		Total laiabilities	$562,500
45	Buildings	$925,000	Stockholders' Equity
46	Equipment	$750,000	Common stock	$50,000
47	Total Cost	$1,675,000	Additional paid-in capital	$400,000
48	Accumulated depreciation	($45,000)	Retained Earnings	$1,162,500
49	Net, Property, Plant and Equipment	$1,630,000	Total Stockholders' Equity	$1,612,500
50	Total Assets	$2,175,000	Total Liabilities & SE	$2,175,000

________________________________________________________________

Working as follows:

	B	C	D	E	F
2	Great Taste Restaurants Inc.
3	Income Statement
4	For the year ended December 31,2016
5	Sales revenue	1200000
6	Operating Expenses:
7	Wages Expense	=C13-SUM(C8:C12)
8	Utilities Expense	35000
9	Rent Expense	75000
10	Advertising Expense	82500
11	Depreciation Expense	50000
12	Loss on Disposal of assets	10000
13	Total Operating Expenses	=C5-C14
14	Income From Operations	782500
15	Other item:
16	Interest Revenue	=C18-C14-C17
17	Interest Expense	-15000
18	Income before Income taxes	769500
19	Income tax expense	192000
20	Net Income	=C18-C19
21
22
23	Great Taste Restaurants Inc.
24	Statement of Stockholders' Equity
25	For the year ended December 31,2016
26		Common Stock	Paid-in capital	Retained Earnings	Total
27	Beginning January 1, 2016	50000	400000	800000	1250000
28	Stock issuance	0	0		0
29	Net Income			=C20	=SUM(C29:E29)
30	Dividends declared			=E31-SUM(E27:E29)	=SUM(C30:E30)
31	Balance December, 2016	50000	400000	1162500	=SUM(F27:F30)
32
33
34	Great Taste Restaurants Inc.
35	Balance Sheet
36	December 31, 2016
37	Assets		Laibilities
38	Current Assets		Current Liabilities:
39	Cash	300000	Accounts payable	75000
40	Short-term investments	50000	Unearned revenue	45000
41	Accounts receivable	=C43-C39-C40-C42	Short-term note payable	92500
42	Supplies	120000	Total-Current Laibilities	=SUM(E39:E41)
43	Total Current Assets	545000	Notes payable	350000
44	Property, Plant and Equipment:		Total laiabilities	562500
45	Buildings	925000	Stockholders' Equity
46	Equipment	750000	Common stock	=C31
47	Total Cost	1675000	Additional paid-in capital	=D31
48	Accumulated depreciation	=C49-C47	Retained Earnings	=E31
49	Net, Property, Plant and Equipment	1630000	Total Stockholders' Equity	1612500
50	Total Assets	=C49+C43	Total Liabilities & SE	=E49+E44