CASH BUDGETING PROBLEM

Kent Resom, a recent University of Phoenix graduate with huge potential, has accepted a position as an entry-level analyst with Builders R Us Manufacturing Company. The president of the company, U. R. Wright, has requested Kent to provide him with a cash flow analysis for the period of April 30 to June 30, which happens to be the end of the current fiscal year. The following information is available:

All sales are credit sales, with 20% of sales collected in the month of sales, 60% collected in the month following sales, 15% collected in the second month following the sale, and the remaining 5% written off as bad debt losses in the sixth month following the sale. Raw materials purchased each month are 75% of the next months sales. Payment for raw materials is made during the month after the purchase. The company is required to maintain a minimum balance of $5,000 in the checking account, and the balance on April 1^st is $6,000. Rent, insurance, and other expenses are $1,000 per month.

Sales and wage data are as follows:

ACTUAL

	Sales	Wages
February	$ 30,000	$ 3,000
March	$ 30,000	$ 3,000

ESTIMATED

	Sales	Wages
April	$ 30,000	$ 3,000
May	$ 40,000	$ 4,000
June	$ 50,000	$ 5,000
July	$ 60,000	$ 6,000
August	$ 50,000	$ 5,000
September	$ 40,000	$ 4,000

Prepare a cash budget for the period April 30 through June 30. For any month during which a cash shortage is indicated, assume that the Friendly Online National Bank will make funds available. Use the budget worksheet provided to solve the problem.

CASH BUDGET WORKSHEET Note: Fill in each light yellow cell FEB MAR APR MAY JUN JUL Cash Collections on Sales Note to students: All cells can be filled with either data from the MS Word doc problem or a formula derived from the problem. Collections ________ ________ ________ ________ ________ ________ Month of Sale (20%) ________ ________ ________ ________ ________ ________ ________ ________ ________ ________ ________ ________ Total Cash Collections ________ ________ ________ Raw Materials Payment Purchases ________ ________ ________ ________ Payment ________ ________ ________ Cash Budget Cash in from collection ________ ________ ________ Cash Out a) Raw Material Purchases ________ ________ ________ b) Rent, Insurance, and Other ________ ________ ________ c) Wages ________ ________ ________ Total Cash Out ________ ________ ________ Cash gain (loss) ________ ________ ________ Beginning Balance without outside financing ________ ________ ________ Cumulative cash without outside financing ________ ________ ________ Required minimum cash ________ ________ ________ Hint for the last two line items: EXCESS CASH ________ ________ ________ There should ONLY be three entries in total for the last two lines; as an example, if you only had one of the years OR that had excess cash, then the other two entries would be blank this would mean there would be two negative entries (CUMULATIVE LOAN NEEDS) ________ ________ ________ in the last line (cumulative loan needs) for the other two years.