Assignment

(A) You have been asked to prepare a cash budget for the next quarter, January through March, for ABC Fashions Ltd, showing monthly cash inflows, cash outflows and monthly cash surplus/ deficit.

They have provided you with the following information:

Sales are expected to be: Rs.400,000 in January, Rs.400,000 in February, and Rs.600,000 in March.

50% of sales are in cash. 50% of Sales are on credit and money in received in the next month.

The estimated purchases are: Rs.380,000 in January, Rs360,000 in February, and Rs.450,000 in March.

Payments for purchases will be made after a lag of one month ie Dec purchases are paid in Jan.

Outstanding on account of purchases in December last are Rs.350,000.

The rent per month is Rs.10,000 and the misc office expenses per month is Rs.25,000.

Salaries and other expenses, payable in cash, are expected to be: Rs.25,000 in January, Rs.20,000 in February, and Rs.30,000 in March.

They plan to buy furniture worth Rs.40,000 on cash payment in January..

The current cash balance with ABC Ltd is Rs 30,000. They always want to keep Rs 30,000 as reserve with themselves to meet any contingency.

(B) Looking at cash budget above how much money should ABC borrow in Jan so that it can function smoothly

(C) How much cash would ABC Ltd have by end of March considering both reserve of Rs 30,000 and borrowings done in (B) above and net monthly cash surplus & deficit

(D) Suppose of March credit sale collections which were to be received in April - due to 1 big large customer going bankrupt 60% of credit sales of March will become bad and thus only 40% of credit sales of March will be collected in April. Suppose in April all other expenses are same as in March. No capital expenditure is being planned etc. And of course March purchases will be paid in April. What will be cash situation in April.

Please submit your answer in excel and keep it short & crisp.