Homework: Cash Budget for Zuber

Zuber Company makes remote controlled toy jets, and it has the following Sales information

Feb-15 (Actual) -$150,000

$ Mar-15 (Actual) -$145,000

Apr-15 (Forecast)-$140,000

$ May-15 (Forecast) -$280,000

$ June-15 (Forecast)-$170,000

$ July-15 (Forecast)-$120,000

Collections: 40% of customers pay cash, 40% pay in month after sale, 20% pay 2 months after sale

Payments: Estimated purchase of inventory is 75% of sales and is made 2 months before sale and paid for 1 month after delivery.

The company has a monthly recurring expense of $14,000.

The company has to pay one-time lease payment of $10,000 due in June.

The company has a Cash Balance of $48,000 on April 1st, 2015.

The company is required by the bank to keep a minimum balance of $40,000.

The short term borrowing rate is 7% annually.

a) Forecast monthly collections and other cash inflows

b) Forecast purchases and other cash outflows

c) Summarize the effect on net monthly cash flows and determine borrowing needs or surpluses every month from April, 2015.

d) Create a Scenario Manager with the following Sales Scenario and compute borrowing and repayment schedule for each month.

Month	Sales (Normal)	Sales (Good)	Sales (Bad)
Apr-15 (Forecast)	140,000	180,000	110,000
May-15 (Forecast)	280,000	330,000	230,000
June-15 (Forecast)	170,000	210,000	140,000
July-15 (Forecast)	120,000	150,000	100,000