ACCTG3750 Accounting Utah State Cash Flow Statement Spreadsheet Model System

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

PossibleEarnedItem

20Preliminary Design Report

10Instaprint Company SpreadsheetIs it done?Are the numbers correct?

10Operating Activities (Direct Method)

10Indirect Method (Reconciliation of Net Income to Cash from operating Activities)

10Financing and Investing Activities

40Generalizability to other cases, usefulness, user friendliness, readability of cash flow statement, protection, graph, check figures.