Using the data presented below:

Mike Owjai Manufacturing
Balance Sheet
For the Year Ended December 31, 2014
Continue to the next page€¦

a. Recreate the income statement and balance sheet using formulas wherever possible. Each statement should be on a separate worksheet. Try to duplicate the formatting exactly.
b. On another worksheet, create a statement of cash flows for 2014. Do not enter any numbers directly on this worksheet. All formulas should be linked directly to the source on previous worksheets.
c. Using Excel€™s outlining feature, create an outline on the statement of cash flows that, when collapsed, shows only the subtotals for each section.
d. Suppose that sales were $2,850,000 in 2014 rather than $2,625,000. What is the 2014 net income and retained earnings?
e. Undo the changes from part d, and change the tax rate to 40%. What is the 2014 net income and retained earnings?
Using the data from the previous problem:
a. Create a common-size income statement for 2013 and 2014. This statement should be created on a separate worksheet with all formulas linked directly to the income statement.
b. Create a common-size balance sheet for 2013 and 2014. This statement should be created on a separate worksheet with all formulas linked directly to the balance sheet.
c. Create a common-size statement of cash flows for 2014 that can be switched between using sales and the 2013 cash balance in the denominator.
Download the file named "Chapter 2 Problem 3.xlsx" from the text support Web site, (www.cengage.com/finance/mayes)
a. Fill in the blanks on the financial statements for Winter Park Web Design with formulas. Some of the formulas will require links to data given in other worksheets in the workbook.
b. Create a common-size income statement and balance sheet using your answers from above. Using the techniques you have learned in this chapter, you should be able to do these statements in less than one minute with formatting that is identical to the original worksheets.

Transcribed Image Text:

Mike Owjai Manufacturing Income Statements For the Years 2013 and 2014 2013 $2,625,000 S2,422,500 1.704,975 1,621,875 920,025800,625 53,250 652,350 626,250 48,750 72,375 51,000 21,375 7,481 S46,751 S13,89.4 2014 Sales Cost of Goods Gross Profit Depreciation Sellng & Admin. Expense Lease Expense 63,000 48,750 155,925 84,000 71,925 25,174 Net Operating Income Interest Expense Earnings Before Taxes Taxes Net Income Notes: Tax Rate Shares Earnings per Share 35.00% 75,000 S0.62 35.00% 60,000 $0.23 2014 2013 Assets Cash Marketable Secuities Accounts Receivable Inventory Total Curent Assets Gross Fixed Assets Accumulated Depreciation Net Plant & Equipment Total Assets Liabilities & Owner's Equity Accounts Payable Accrued Expenses Total Curent Liabilities Long-tenm Debt Total Liabilities Common Stock (Si.00 par) Additional Paid-in-Capital Retained Eamings Total Owner's Equity Total Liab. & Owner's Equity $39,000 1,826 315,000 386,250 742,076 2,010,000 426,750 1,583,250 52.325,326 $30,750 15,750 279,000 315,000 640,500 1,627,500 363,750 1,263,750 S1,904,250 378,750 26,250 405,000 878,325 1,283,325 75,000 518,250 448,751 1,042,001 S2,325,326 217,500 22,500 240,000 795,750 1,035,750 60,000 406,500 402,000 868,500 S1,904,250