ACCTG3750 Accounting Utah State Cash Flow Statement Spreadsheet Model System Cash Flow Statement Spreadsheet Model System Individually In this project yo
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 OKeefe 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. 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 OKeefe
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
