Reference no: EM131862942
You need this done on a spreadsheet for cash Budget!
(25) Cash Budget for New Toys Company : Prepare a cash budget and provide advices for the second quarter of 201x based upon the following information:
The firms' sales during February and March of 201x were $1,528,000 and $1,662,000, respectively. Sales during the next four months are projected to be:
April $1,950,000 May $2,700,000
June $2,400,000 July $2,100,000
Historically, 100% of the firm's sales are on credit, with about 60% of each month's sales collected one month after the sale and the remainder collected two months after the sale.
New Toys purchases raw materials typically about 35% of sales one month ahead of sale. Purchases are generally paid 20% with cash, 50% in one month and the remaining 30% in two months after the purchase.
The firm's monthly wages, salaries and other fringe benefits including contributions to the firm's 401-(k) plan are $100,000 (fixed) plus 25% of monthly sales.
Marketing expenses are about 3% of each month's sales and will be paid in the following month.
Rent payments of $25,000 are due each month.
Factory overhead which is $25,000 plus 2% of monthly sales are due each month.
Estimated income taxes of $100,000 are due in June.
The firm plans to purchase a new machine for $800,000 in April.
Term loan (principal) of $1,000,000 is due in May (i.e., paying off the term loan).
Interest rate on the term loan is 6.0% APR and interest payments are due each month.
The firm owns a $350,000 CD and it matures in June.
New Toys has a $2,000,000 evergreen line of credit with a bank at 3.6% APR and has $300,000 outstanding balance as of March 31. The firm pays off the balance whenever cash surplus is available, but it has to pay (monthly) interest on the outstanding balance one month later.
At the end of March, the firm has $250,000 in cash. This is also the firm's target monthly ending cash balance.