The Excel file provides the sales forecast. Collections estimates were obtained from the

accounts receivable department and are listed below. Organa does not offer discounts for early

payment. Additionally, you will note that the collection rates only sum to $98\%.$ This is because

$2\%$ of the sales are never collection $($bad debt$).$

$•15\%$ collected in the month of the sale

$•70\%$ collected in the month following the sale

$•13\%$ collected in the second month following the sale.

The Excel files provides the estimates for raw materials purchases and labor costs. Payments

for the raw materials and labor are typically made in the month following the purchase $($i$.$e$.,$

one$-$month lag$).$

Requirements:

$1.$ Prepare a monthly cash budget for January through April. Be sure to clearly show the

surplus $($deficit$)$ of cash each month.

$2.$ Based on the deficit cash shown on your cash budget $($if you have one$),$ what is the

dollar amount that Organa should have for a line of credit over this $4-$month period?

Note: don$’$t add a $“$cushion$”,$ just base this amount off of the cash budget.

 

INPUTS Collections during month of sale 15% Collections during month after sale 70% Collections during second month after sale 13% Lease payments $11,790 General and administrative salaries $84,320 Depreciation charges Tax payments (March) Target cash balance Miscellaneous expenses Cash on hand January 1 $9,700 $22,000 $5,000 $18,200 $45,000 November December January February March April Sales estimates $703,500 $514,700 $359,400 $327,000 $459,000 $416,400 Labor and Raw Material Estimates $294,708 $268,140 $376,380 $341,448 $0