High Tech Resources Use High Tech Resources as prototype for developing your Cash Flow Statement Spreadsheet Model Increase Balance Sheet 2012 2011 (Decrease) Income Statement 2011 (in thousands) Sales Cost of Goods Sold Gross Profit Operating Expenses: Selling Depreciation Expense Bad Debt Expense General and Admin Total Operating Income Other Revenues & Expenses Interest Expense Gain from sale of LT. Inv. Loss on sale of Equip. Total Income Before Income Taxes Provision for Income Taxes (34%) Net Income Other Details for Cash Flow Statement: Depreciation Expense Common Stock Issued for Equipment Cash payed on Long-Term Notes Payale Cash from Sale of Treasury Stock Cash Purchase of Equipment Cash from sale of Long Term Investments Proceeds from sale of Equipment Cash Paid for Dividends Bad Debt Exp Adj Cash from Sale of Common Stock 352,000 (184,000) 168,000 Assets Current Assets: Cash Marketable Securities Accounts Receivable (Net) Inventories Prepaid Insurance Interest Receivable Total Current Assets (3,500) (1,000) (87,600) (92,100) Equipment Less Accumulated Depreciation Net 75,900 (1,400) 1,000 (500) (900) Long Term Investments Total Assets 75,000 (18,800) 56,200 (3,500) 6,000 (4,000) 5,000 (4,500) 6,400 (2,000) 1,000 Cash Received from Customers Sales Add Beg Net A/R Less End Net A/R Less Bad debt exp adj. Less Beg unearned Rev Add End Unearned Rev Total 352,000 13,300 (15,000) (1,000) 349,300 Cash Paid for Inventory Cost of Goods Sold Add End Inventory Less Beg Inventory Equal Purchases Add Beg A/P Less End A/P Total 184,000 10,500 (12,700) 181,800 5,600 (3,500) 183,900 88,200 15,000 10,500 2,800 116,500 29,000 13,300 12,700 2,000 57,000 59,200 1,700 (2,200) 800 59,500 40,000 (9,500) 30,500 33,000 (9,000) 24,000 7,000 x (500) x 6,500 3,000 8,400 (5,400) x 150,000 89,400 60,600 Liabilities & Equity Current Liabilities Accounts Payable Accrued Liabilities Income Taxes Payable Short Term Notes Payable Unearned Revenue Interest Payable Total Current Liabilities Notes Payable Long-term Total Liabilities 3,500 6,000 500 10,000 8,000 18,000 5,600 4,000 1,000 10,600 12,000 22,600 (2,100) 2,000 (500) (600) (4,000) (4,600) x Stockholders' Equity Common Stock Paid in Capital Retained Earnings Treasury Stock (less) Total Stockholders Equity 55,000 16,000 66,000 (5,000) 132,000 50,000 15,000 11,800 (10,000) 66,800 5,000 1,000 54,200 5,000 65,200 x x x x Total Liabilities and Equity 150,000 89,400 60,600 Cash Paid for Operating Expenses Operating Exp Less Depreciation Exp Less Beg Prepaid Insurance Add End Prepaid Insurance Add Beg Accrued Liabilities Less End Accrued Liabilities Less Bad Debt Adj. Total Cash Received from Interest Interest Revenue Add Beg Interest Receivable Less End Interest Receivable Total 349,300 (183,900) (88,400) (1,900) (16,800) 58,300 Cash Flows From Investing Activities: Cash from sale of Long Term Investment Cash from Sale of Equipment Cash Paid for Purchase of Equipment Net Cash Provided (Used) by Investing Activities 6,400 (4,500) 1,900 Cash Flows from Financing Activities: Proceeds (+) or Payment (-) of long term debt Proceeds(+) or Payment(-) for Treasury Stock Payment of Cash Dividends (4,000) 5,000 (2,000) Net Cash Provided (Used) by Financing Activities (1,000) Net Increase in Cash and Cash Equivalents (Marketable Securities) 59,200 Schedule of Noncash Investing and Financing Activities Issued Common Stock for Equipment 6,000 Cash Flows from Operating Activities Indirect Method: Net Income Adj. To reconcile NI to net cash provided by operating activites: Depreciation Expense Change in Accounts Receivable Change in Inventory Change in Pre-paid Expenses Change in Interest Receivable Change in Accounts Payable Change in Taxes Payable Change in Unearned Revenue Change in Interest Payable (Gain) or Loss on sale of LT Inv. (Gain) or Loss in Sale of Equipment Net Cash Provided (Used) by Operating Activities Equip Acct beg equip purchase with stock equip purchase with cash Equip disposal End Bal Acum Depre Beg Deprec exp acum deprec disposal Ending Bal 33,000 6,000 39,000 4500 -3500 40,000 9000 3500 -3000 9500 DR x x Cash Paid for Income Taxes Income Tax Exp Add Beg Inc. Tax Payable Less End Inc. Tax Payable Total - 56,200 3,500 (1,700) 2,200 (800) (2,100) 2,000 (500) (1,000) 500 58,300 Is direct method equal to indirect method? You got it right baby Is Net increase in Cash same as change in cash on the camparative balance sheet? Great Job Jnl entry for disposal of Equip cash for equip Equip dispse acum dep loss on equip x Cash Paid for Interest Interest Exp Add Beg Interest Payable Less End Interest Payable Total 92,100 (3,500) (2,000) 2,800 (1,000) 88,400 Cash Flow Statement Cash Flows from Operating Activities Direct Method: Cash Received from Customers Cash Received from Interest Cash Payments for Inventory for Resale Cash Payments for Operating Expenses Cash Payments for Interest Cash Payments for Taxes Net Cash Provided (Used) by Operating Activities x x x x x x 1,400 1,000 (500) 1,900 18,800 4,000 (6,000) 16,800 CR 3500 3000 500 O'Keefe Inc. Compartive Income Statements For the Year Ending December 31, 2012 and 2011 (In Thoursands of Dollars) Sales Cost of Goods Sold Gross Profit Operating Expenses: Selling General and Administrative Total Operating Expenses Operating Income Other Revenues and Expenses: Interest Income Interest Expense Total Use O'Keefe Inc. as a prototype for developing your Cash Flow Statement Spreadsheet Model O'Keefe Inc. Statement of Cash Flows For the Year ending December 31, 2012 (In Thoursands of Dollars) 2012 2011 Cash Flows from operating activities 3,000 2,500 Net Income (2,600) (2,300) Adjustments to reconcle net income 400 200 to net cash provided by operating activities Depreciation Expense Increase in Accounts Receivable (125) (105) Increase in Inventory (70) (60) Increase in Interest Receivable (195) (165) Increase in Accounts Payable Increase in Income Taxes Payable 205 35 Increase in Interest Payable Total Adjustments Net cash provided (used) by operating activities 10 5 (40) (20) Cash flow from investing activities (30) (15) Payments for purchase of equipment Income Before Taxes Provision for Income Taxes (34%) Net Income 175 (60) 115 20 (7) 13 Earnings Per Share 1.28 0.19 $ $ 50 (45) (50) (5) 105 40 5 Cash flow for financing activities Proceeds from issuance of common stock Proceeds from issuance of long-term debt Payments on long-term debt Payment for loan to President Payment of cash dividends Net cash provided (used) by financing activities $ $ $ $ $ 2011 Increase (Decrease) Assets: Current Assets: Cash Marketable Securities Accounts Receivable (Net) Inventories Interest receivable Total Current Assets 300 75 325 400 60 1,160 270 45 280 350 55 1,000 30 30 45 50 5 160 Property, plant, and equipment less accumulated Depreciation Net 1,500 (800) 700 1,300 (750) 550 200 (50) 150 Other Assets 15 12 3 Total Assets 1,875 1,562 313 Liabilities and Stockholders' Equity Current Liabilities: Accounts Payable Income Taxes Payable Interest Payable Total Current Liabilities 435 45 50 530 330 5 45 380 105 40 5 150 Long-Term Debt 300 250 50 Total Liabilities 830 630 200 Stockholders' Equity Common Stock ($1 par) Premium on Common Stock Retained Earnings Total Stockholers' Equity 90 15 940 1,045 70 10 852 932 20 5 88 113 Total Liabilities and Stockholders' Equity 1,875 1,562 313 $ $ $ $ $ 50 27 15 $ $ $ $ $ 200 20 70 3 - 190 10 100 215 $ (190) $ 35 15 70 (20) (3) (27) Net increase (decrease) in cash and cash equivalents 2012 $ (190) Net cash provided (used) by investing activities O'Keefe Inc. Comparative Balance Sheets December 31, 2012 and 2011 (In Thousands of Dollars) Additional Information (In Thoursands of dollars) Depreciation Expense during 2012 Dividends declared and paid during 2012 Common Stock Issued at par for cash in 2012 Equipment acquired Paid cash for equipment Issued comon stock for equipment Total Equipment acquired Long Term debt paid off in cash Issued new Long Term debt for cash Loan extended to President of company Bad Debt Expense Adjustment 115 60 Cash and Cash Equivalents December 31, 2011 315 Cash and Cash Equivalents December 31, 2012 375 Schedule of noncash Investing and Financing Activities: Issue Common Stock in Exchange for Equipment 10 Cash Flow from operating activities (Direct Method) Cash Received from Customers Cash Received from Interest Cash Payments for Inventory Cash Payments for Operting Expenses Cash Payments for Interest Cash Payments for Income Taxes Net cash provided (used) by operating activities Cash Received from Customers Sales Add Beg Net A/R Less End Net A/R Less Bad debt exp adj. Less Beg unearned Rev Add End Unearned Rev Total Cash Received from Interest Interest Revenue Add Beg Interest Receivable Less End Interest Receivable Total 2,955 5 (2,545) (145) (35) (20) 215 3,000 280 (325) - 2,955 10 55 (60) 5 Cash Paid for Inventory Cost of Goods Sold Add End Inventory Less Beg Inventory Equal Purchases Add Beg A/P Less End A/P Total Cash Paid for Operating Expenses Operating Exp Less Depreciation Exp Less Beg Prepaid Insurance Add End Prepaid Insurance Add Beg Accrued Liabilities Less End Accrued Liabilities Less Bad Debt Adj. Total 2,600 400 (350) 2,650 330 (435) 2,545 195 (50) 145 Cash Paid for Interest Interest Exp Add Beg Interest Payable Less End Interest Payable Total 40 45 (50) 35 Cash Paid for Income Taxes Income Tax Exp Add Beg Inc. Tax Payable Less End Inc. Tax Payable Total 60 5 (45) 20 Instaprint Corporation Income Statement For the Year Ending 2012 Income Statement Sales Cost of goods sold Gross profit Operating Expenses Operating income Other revenues and expenses: Interest income Gain on Sale of Land Interest expense Income before income taxes Provision for income taxes Net income Use Instaprint as the Company on your Final Cash Flow Statement Spreadsheet Model 2012 2,902,000 (1,662,000) 1,240,000 (968,000) 272,000 20,000 30,000 (34,000) 288,000 (118,000) 170,000 Instaprint Corporation Comparative Balance Sheets December 31, 2012 and 2011 2012 2011 Assets Current Assets: Cash Marketable securities Accounts receivable (Net) Inventories Interest receivable Prepaid expenses Total current assets Land, buildings, and equipment Accumulated depreciation Total fixed assets Patents 530,000 606,000 792,000 108,000 2,036,000 1,606,000 (852,000) 754,000 192,000 578,000 822,000 152,000 1,744,000 1,500,000 (756,000) 744,000 100,000 - 2,890,000 2,488,000 Current Liabilities: Accounts payable Income taxes payable Interest payable Accrued liabilities Short-term notes payable Unearned revenue Total current liabilities 342,000 112,000 146,000 600,000 382,000 70,000 200,000 652,000 Long-term debt 248,000 - 848,000 652,000 410,000 1,632,000 2,042,000 310,000 1,526,000 1,836,000 2,890,000 2,488,000 Total assests Liabilities and stockholders' equity Liabilities: Total liabilities Stockholders' equity: Common Stock and paid-in capital Retained earnings Teasury Stock Total stockholders' equity Total liabilities and stockholders' equity Additional Information Depreciation Expense for the year Cash dividends declared and paid during the year Land Acquired ten years ago: Initial Cost Land Acquired ten years ago: Sold During the year Equipment purchase during the year A patent was acquired in exchange of common stock 96,000 64,000 20,000 50,000 126,000 100,000 Cash Flow Statement Spreadsheet Model System Individually In this project you will apply some of the principles of system design and implementation to develop a useful cash flow statement template using an electronic spreadsheet. You must prepare a preliminary design or specification report that includes: • General description of the system both in terms of the business problem and the system aspects. • Identify the objectives, scope and benefits of the system (both in general what the system will do for solving the problem and what little features you have or incorporated into your template. Make sure you describe the benefits of the cash flow statement for business decision makers. • Identify system Requirements: • Inputs and source of data (Financial statements. Make sure you have a data section in your spreadsheet) • Processes: (Converting accrual financial data into cash basis) • Outputs: (Useable, easy to read cash flow statement You should design the system so that a user could easily follow the computations of how the inputs are converted into the output values. Your spreadsheet model should have an input section in which you can type in the input data. Any cells, other than labels, outside the input section cannot use typed in values. The sections outside the input section must use some type of formula, cell reference, lookup, calculation, etc. that changes automatically based on changes in data inputs. The template should be very user friendly so that formulas are easy to follow rather than just a collection of cell references. Labels should be used to help identify how cash flow statement formulas are used. Since we are using an electronic spreadsheet, the detailed requirements are somewhat built into the system. For example, you really do not need to specify how many digits, etc., there will be in each column. 1. Prepare a prototype computerized cash flow statement spreadsheet model for O’Keefe Inc. and/or High Tech Resources cases found in the cash flow data pdf file. The solutions for these are provided to you so you will have a complete example to help you check your prototype spreadsheet for accuracy. Your template should use formulas as much as possible, so the template can be used for other cases. Your template should include the Balance Sheet for two years (and the changes), the Income Statement, other details as the input. The cash flow statement should include cash flows from operating activities using the direct method and the indirect method (show both), cash flows from investing activities, cash flows from financing activities, schedule of noncash investing and financing activities, and the net increase (decrease) subtotals for each activity area as well as the grand total for net increase or decrease in cash. Your spreadsheet prototype template should also include a built in way to check to see whether or not the indirect and direct methods for cash from operations are equal. You should also have a built in method for checking whether the cash flow total amount is equal to the change in cash and cash equivalents on the balance sheet. Finally your spreadsheet should include some kind of graph and also protection on the cash flow formulas and cash flow statement cells, but not on the data input cells. (It is easiest if you put protection on last.) 2. Once you have prepared the template (O,’Keefe or High Tech or both), use the template to complete the cash flow statement for Instaprint Corporation (data included in the cash flow data pdf file). If you have made a good template form prototype companies’ data, completing the Instaprint Corporation problem should not take very long. However there will be some changes to some formulas. 3. Using online course system, hand in your work by attaching your Instaprint Corporation cash flow statement spreadsheet model and your preliminary design report files electronically. Name the files with your last name and first initial followed by an underscore and CF for “cash flow.” Assignment Grading Points Possible 20 10 Item Preliminary Design Report Instaprint Company Spreadsheet Is it done? Are the numbers correct? Operating Activities (Direct Method) Indirect Method (Reconciliation of Net Income to Cash from operating Activities) Financing and Investing Activities Generalizability to other cases, usefulness, user friendliness, readability of cash flow statement, protection, graph, check figures. 10 10 10 40 ____ 100 Earned ______ Total 
Purchase answer to see full attachment