ACCT 3170 Dr. D. G. Duncan

HOMEWORK 1

FLOWCHART HOMEWORK PROBLEM

CASH DISBURSEMENTS TRANSACTIONS

The flowchart attached illustrates a manual system for executing purchases and cash disbursements transactions. Indicate what each of the letters A through L represents. Word process your solution.

This problem has been used as a CPA exam question.

HOMEWORK PROBLEM 2

DRAWING A FLOWCHART FOR INVENTORY SYSTEM

The AIS Company maintains a perpetual inventory system. Clerks in the accounting department post the data manually from receiving reports, material requisition forms, copies of purchase orders, and other transactions, such as returns and adjustments to the inventory records. The source documents are filed by posting date. The inventory records are analyzed after each posting to determine if the item should be reordered. If an item needs to be reordered, a purchase requisition (one copy) is prepared and sent to the purchasing department. There, clerks select a vendor from a master vendor file, prepare a purchase order (four copies). And update the vendor files to reflect the order. The purchase order is approved and distributed as follows: original copy to the vendor; copy 2 is filed numerically with the corresponding purchase requisition attached; copy 3 is forwarded to the receiving department; copy 4 is sent to the accounting department.

FLOWCHART HOMEWORK PROBLEM 2: Draw a flowchart for the proceeding problem. You are to use graphical software.

SPREADSHEET APPLICATION PROBLEM

HOMEWORK SET 3

Personal Budget

Prepare a personal budget for 2008. Enter the labels and values in the worksheet as shown in the accompanying template. Also, include your name, and the section. Change the width of column A to 13 to accommodate the length of the labels. Enter only formulas to complete the worksheet through December, using the relationships below. Use/Copy whenever possible. Use the spreadsheet columns and row numbers indicated.

All income and expenses are the same as the previous month, except:

1. Salary is expected to permanently increase in July. The increase is shown in cell C 14.
2. Food, clothing, fuel, utilities, and misc. are expected to increase with a monthly rate of inflation starting in February (cell C 13)

Complete the following steps:

1. Two items were left out of the expenses. Car payments and vacation. Car payments are fixed at $180 per month. Enter this in row 34. Enter vacation expense of $1500 in April and August in row 35

2. Format the dollar values as comma, with zero decimals. Provide for $ on appropriate lines.

3. Cross total each income and expense line (and their totals) in column N, and format the column in the same way as the other data. So not cross total and other lines.

4. Enter the Labels BORROW, REPAY and DEBT in cells A40, A41 and A42, respectively. Have the bank automatically lend you (interest free) enough money at the end of each month to bring your beginning balance to $250 whenever the ending balance for the preceding month drops below that figure. Also you want to repay the loan with funds in excess of the $250 ending balance figure for as long as you are in debt. The amount you repay should be subtracted from the beginning balance for the month.

You use a @IF statement in cell B40 to borrow the different between $250 and the ending balance if that balance for the current month is less than $250; otherwise, borrow nothing.

The repay figure in January will be zero, since you have no starting debt. In the remaining months, you will repay something on the debt if two conditions are met: (1) Your ending balance in greater than $250 and (2) the debt figure from the previous month is greater than zero; otherwise, you will repay nothing. If these two conditions are met, you will repay the smaller or a) the amount owed, or b) the amount available for repayment. Use both compound and nested @IF statements.

The debt for January will be whatever is borrowed in that month. Thereafter, the debt will be the previous month’s debt plus whatever is borrowed in the current month minus whatever is repaid in the current month.

Beginning balance beginning with February will be the prior month’s ending balance plus borrows less repays.

5. After you have entered the proper formulas and formatted these cells, check the figures to ensure they are correct. Then print the output using condensed print. Next, print the cell formulas.

6. Reminder, no credit will be given unless your name and section are included in the body of the spreadsheet above the data.

	A	B	C
10	Cash Budget -- 2008
11	Name and Section
12
13	Exp. Monthly Inflation 0.008
14	Expected Sal. Increase .05 of budget on version one, .25 increase on version two (print out both versions)
15
16	Month	Jan	Feb
17	- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
18	Beginning Balance	700
19
20	Cash In
21	- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
22	Salary	2300
23	Interest	275
24	Total In
25
26	Cash Out
27	- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
28	Rent	1550
29	Food	450
30	Clothing	125
31	Fuel	180
32	Utilities	110
33	Misc.	350
34
35
36	Total out
37
38	Ending Balance
39
40	Borrow
41	Repay
42	Debt

SPREADSHEET APPLICATION PROBLEM

HOMEWORK SET 4

Cash Flow

Over the past several years, the Sportswear Corporation, a sports wear retailer has encountered difficulties estimating its cash flow. The result has been a strained relationship with the banker. The controller would like to develop a means by which he can project the firm’s monthly operation cash flows. The following data were gathered to facilitate the development of such projection:

1. Sales have been increasing at the rate of .8 percent each month in the past, and are expected to continue in the future at the same rate.

2. 20% of each month’s sales are for cash; the other 80% are on open account.

3. Of the credit sales, 65% are collected in the first month following the sales, and the remaining 35% are collected in the second month. There are not bad debts.

4. Cost of goods sold is 75% of sales and includes only the cost of inventory. Thus, the gross margin on sales is 25%

5. The company purchases enough inventory each month to cover the estimated following month’s sales at cost.

6. All inventory purchases are paid for the month of purchase.

7. Monthly expenses are: Payroll -- $3,500; Rent -- $800, Depreciation -- $600; other cash expenses – 1 ½% of the current month’s sales.

8. January 2008 sales are estimated to be $60,000, based on the growth rate given above.

9. The controller notes that the buffer inventory level is larger than required. Reduce the March inventory purchase by $10,000 (one time adjustment).

10. Include a line for investments. In February you will purchase 200 shares of General Motors stock at $90 per share, with a $10 per share annual dividend, paid in quarterly installments, received in March, September, June and December.

11. Beginning in February, invest net available cash in excess of $1,200 in money market funds with a return of 6% annually. Prepare a line for the interest income received monthly from these funds. Compound interest. Beginning balances after July will always be the same as prior month’s ending balance.

Money market funds will be purchased on their first day of each month based on the net available cash at the end of the preceding month. Interest is earned for the entire month and is received on the first day of the month following. Interest is earned on current month purchases as well as all prior month’s purchases. Interest earned is added to the balance on which each subsequent month’s interest is calculated (compounding).

Example: Assuming the ending cash balance for January calls for a $1000 investment in money market on February 1 Interest of $10 on the purchase is received on March 1. The balance for calculating April interest would be arrived at by taking the March and February investments of $1000 each, and adding the $10 on February interest which was earning interest in March.

	Jan	Feb	Mar	April
Cash In			10	20.10
M/m Interest			($1,000 @ 6%)	($2010 @ 6%)
Cash Out
M/m Purchases		1000	1000	1000
		(Feb 1)	(March 1)	(April)
M/m Balance for Interest
Calculation		1000	2010	3030

Assignment:

Using the preceding data, follow these steps:

1. Develop a model the controller can use for his calculations. Your model should be capable of calculating the monthly cash flows for any specified month. Format the dollar values as comma, with zero decimals. Also provide for $ on appropriate lines. Use formulas and assumptions only. No absolute numbers are allowed in the worksheet itself. See suggested format attached.
2. Print a projection of the firm’s cash budget for the six months, January through June, 2008. Provide a descriptive title, and add your own name, course number and section.
3. Make a bar graph of the net cash flow per month for January through June. Use the proper titles for the main graph, X axis and Y axis. On all graphs, include your own name and course/ section numbers.
4. Drop the oldest month and add the next month, July, using the /Copy command. Do not delete January from the worksheet. But use the WS/column/hide command to delete the data from the printout. This should result in moving the February figures to the left adjacent to the line descriptions.
5. Print the revised worksheet.
6. Print the cell formulas for the entire worksheet, including the assumptions and non-cash flow items used in the calculations.

NOTE: You will need sales and cost of sales lines in order to calculate some of the answers. You will also need these figures for November, December 2007 and For August 2008. Place these lines and the assumptions used and an adjacent section of EXCEL. So they can be printed out separately from the cash flow statement itself. They MUST be printed out with items b and e above, in order to support the cash flow projections.

Remember to incorporate your name, class number, and section in the graphs and worksheets.

Sportswear Corp. Cash Budget
January 2008 - June 2008
Name And Section
Month	Jan	Feb	March	April	May	June
Beginning Balance	0
Cash In
Current Month Cash Sales
Prior Month Credit Sales
2 Months Prior Credit Sales
Dividends
Money Market Interest
Total In
Net Cash Flow
Cash Out
Inventory Purchases
Payroll
Rent
Other Expenses
Investment in Stock
Investment in Money Market
Total Out
Ending Balance
Money Market Balance
Sales
Cost of Sales
Assumptions
Place all absolute numbers used as
Assumptions in a separate work sheet.

QuickBooks Extra credit:

1. Set up the personal budget (homework 3) using QuickBooks Pro. (2 points)

2. Set up the Sportswear cash flow problem (homework 4) using: QuickBooks Pro. (2 points)

Submit one or both of these at the same time.

3170_homework_all_rev_100112.doc