HOMEWORK PROBLEM 1

DRAWING A FLOWCHART FOR INVENTORY SYSTEM

 

The CIS 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 1:  Draw a flowchart for the proceeding problem. You are to use graphical software.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

HOMEWORK 2

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 3

FLOWCHART HOMEWORK PROBLEM

CHARGE SALES APPLICATION

 

A partially completed charge sales system flowchart is attached. The flowchart depicts the charge sales activities of the Widget Manufacturing Corp.

 

A customer’s purchase order is received and a six-part sales order is prepared from it. The six copies are initially distributed as follows:

 

Copy 1 – Billing copy –to billing department

Copy 2 – Shipping copy – to shipping department

Copy 3 – Credit copy –top credit department

Copy 4 – Stock request copy – to credit department

 

When each copy of the sales order reaches the applicable department or destination, it calls for specific internal control procedures and documents are labeled letters a to r.

 

List the procedures on the internal documents that are labeled letters c to r in the flowchart of Widget Manufacturing Corporation’s change sales system.

 

Organize and word process your answer as follows (an explanation of the letters a and b, which appear in the flowchart, are entered as examples):

 

 

 

Flowchart symbol (letter)                                                 Procedures or Internal Document

 

a.                                                                                       Prepare six-part sales order

b.                                                                                       File by order number

 

This problem has been used as a CPA exam question.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


                 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SPREADSHEET APPLICATION PROBLEM

HOMEWORK SET 4

Personal Budget

 

Prepare a personal budget for 2007. 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

 

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

 

  1. 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.

 

  1. 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 pervious 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.

 

  1. 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.

 

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

 

7.                  Extra credit: do this problem using Quickbooks Pro.


 

 

 

A

B

C

 

10

Cash Budget -- 2007

 

 

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 2

HOMEWORK SET 5

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.

 

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

 

 

  1. 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.

 

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

 

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

 

 

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

 

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

 

 

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

 

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

 

 

  1. 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.

 

  1. Beginning in February, invest net available cash in excess of $12,000 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.

 

  1. Extra credit: do this problem using Quickbooks Pro.

Sportswear Corp. Cash Budget

 

 

 

 

 

 

January 2007 - June 2007

 

 

 

 

 

 

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

 

 

 

 

 

 

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.