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.

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.

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 cation.com /com tion Relationships among Financial Statements - Excel PAGE LAYOUT FORMULAS DATA REVIEW HOME INSERT VIEW Calculate Now on Defined Formula Name Adding calculation occulate Sheet Options Cication fc Use a cell reference to display the Net Income value you produced in letter a. under the total column. Solve for the missing Dividends Declared under the Retained Earnings column. Use a cell reference to display the Dividends Declared value you calculated in letter c. under the total col Use a SUM function to calculate the Total Stockholder's Equity Great Taste Restaurants Inc. Statement of Stockholders' Equity For the year ended December 31, 2016 Paid-in Common Stock Capital Retained Earnings $ 50,000 $ 400,000 S 800,000 S 0 0 Total 1.250,000 Beginning January 1, 2016 Stock Issuance Net Income Dividends Declared 6 Balance December 31, 2016 S 5 0.000 400.000 S 13162.500 Balance Sheet Solve for the Accounts receivable, Accumulated depreciation and Total Asset values. Use the SUM function to calculate the total current liabilities call reference in show values for the Common Stock Additional paid in cantal and Retained READY Atemps Chapter 1 Excel Simulation * + ducation.com/flow/connect.html tation 6 HSS LE HOME INSERT Relationships among Financial Statements - Excel PAGE LAYOUT FORMULAS DATA REVIEW VIEW Calculate Now stion Defined Formus aryNames' Auditing Calculation calculate Sheet Options Calculation 67 X x Total Cost Use cell references to show values for the Common Stock, Additional paid-in capital, and Retained Earnings accounts. 2 Solve for the Total Liabilities and SE. S 75,000 45,000 92.500 57 Assets 58 Current Assets: 59 Cash 50 Short-term investments 61 Accounts receivable 62 Supplies 63 Total Current Assets 64 Property and Equipment: 65 Buildings 66 Equipment 67 Total Cost 68 Accumulated depreciation 69 Net Property and Equipment 70 Total Assets Great Taste Restaurants Inc. Balance Sheet December 31, 2016 Liabilities Current Liabilities: S 300,000 Accounts payable 50.000 Unearned revenue Short-term notes payable 120,000 Total Current Liabilities 545,000 Notes Payable Total Liabilities 925,000 Stockholders' Equity 750.000 Common Stock 1.675.000 Additional paid in capital Retained Earnings 1.630,000 Total Stockholders' Equity Total Liabilities and SE 350,000 562,500 1,612,500 . . . Sheet1