ACME Heating and Cooling Company Cash Budget 2010 2011 WORKSHEET November December January February March April May June July Sales Collections: Cash (20%) First Month: (20%) Second Month (60%) Total Collections Purchases (55% of second month?s sales) Payments (one month lag) CASH BUDGET Cash Receipts Total Collections Cash Disbursements Purchases Wages & Salaries Rent Other Expenses Interest Expense (on new debt) Taxes Purchases of Equipment Loan Repayment Total Disbursements Net Monthly Change in Cash Plus: Beginning Cash Balance Equals: Ending Cash Bal. before ST borrowing Financing Needed Ending Cash Balance Cum Debt,Prepare a Cash Budget for the first 6 months of 2011 usng the following information: 1 ACME Forecasted Sales are as follows in 2011: January February March April May June July August 100,000 95,000 88,000 88,000 98,000 111,000 111,000 125,000 2 Historical Sales for 2010 were: November December 97,000 123,000 3 20% of ACME’s Sales are CASH. 20% is collected the month after the sale, and the remaning 60% is collected the second month after the sale. SO for example if in January $100,000 sales were made, $20,000 would be colleced in January, $20,000 in February and the remaining $60,000 in March. 4 ACME raw materials are 55% of the final sales price and they are all purchased 2 months prior to the forecasted sale. Those purchases are paid for the month after the purchase is made. So for example in November 2010 $55,000 of Raw Materials would have been purchased. $55,000 would have been paid to the Supplier in December 2010. 5 ACME monthly expenses are: Rent $12,000 Salaries $16,500 6 ACME has to purchase two new delivery trucks in March at $75,000 7 ACME pays Quarterly Taxes of $4,000 in January, April, July and October 8 ACME has a pre existing note to pay back in April 2011 of $35,000 9 ACME maintains a minimum cash balance of $20,000. Anyshprtfall must be borrowed on a short term basis at $1.25% per month Question The Marketing Manager has asked for $45,000 advertsising spend in August 2011 that must be prepaid on July 1 2011 to secure the advertising space. Does ACME forecast that it will have the cash to make that payment?,Breakeven Questions 7 San Diego Cake Store manufactures the finest Red Velvet Cakes in the world. The Cakes are each sold for $45. Variable costs are 65% of Sales and Fixed Costs are $405,000. How many Red Velvet Cakes must San Diego Cake Store sell in order to break even? 8 ACME heating company expects to make $40,000 in earnngs after taxes (EAT) with Sales of $400,000. Heaters are sold for $20 each. The Variable Cost per heater is $10 and ACME pays 35% taxes. 8.1 What are ACME’s Fixed Costs expected to be next year? 8.2 Calculate ACME’s Break even number of heaters in units and dollars.,Calculate the Future Values of the following: $6,000 invested for 13 years at 3% $24.50 invested for 4 years at 8% $1,000 invested for 15 years at 11% Calculate each one with the Financial Tables and then agan using Excel functions Calculate the Present Values of the following: $15,000 to be received 12 years from now discounted back to the present at 5%. $10,000 to be received 6 years from now discounted back to the present at 11%. $400 to be received 10 years from now discounted back to the present at 3%. Calculate each one with the Financial Tables and then agan using Excel functions Calculate the Future Values of the following Ordinary Annuities: $1,000 a year for 5 years compounded at 7%. $500 a year for 20 years compounded at 5% $25 a year for 4 years compounded at 12% Calculate each one with the Financial Tables and then agan using Excel functions Calculate the Present Value of the following Ordinary Annuities $4,000 a year for 15 years discounted back to the present at 5% $125 a year for 11 years discounted back to the present at 3% $3,500 year for 5 years discounted back to the present at 8% Calculate each one with the Financial Tables and then agan using Excel functions In 10 years I am going to retire and buy condo in Naples, Florida. The condo has recenty been appraised at $400,000. It is estimated to ncrease in value by 5% per year. Assuming I can earn 7% per year how much do I have to invest each year to have enough to buy my dream house? Note this is a Future Value Annuity question You borrow $100,000 at 10% compounded annually from the bank to buy a yacht. Ths loan is to be repaid in 5 years in 5 equal annual installments How much will each annual payment be? Note – this is a loan amortization question solving for PMT in a Present Value Annuity question.