Bugger Enterprises projected sales for the first six months of 2008 are given be

Question

Bugger Enterprises projected sales for the first six months of 2008 are given below:
Jan: $400,000 April $450,000
Feb: $540,000 May $480,000
Mar. $350,000 June $520,000

30% of sales are collected in cash at the time of sale, 60% are collected in the month following the sale, and the remaining 10% are collected in the second month following the sale. Cost of goods sold is 70% of sales. Purchases are made in the month prior to sales and payments for purchases are made in the month of the sale. Total other cash expenses are $50,000/month. The company's cash balance as of 2/28/08 will be $30,000. Excess cash will be used to retire short term borrowing (if any). Bugger has no short term borrowing as of 2/28/08. Assume that the interest rate on short term borrowing is 1% per month. The company must have a minimum cash balance of $20,000 at the beginning of each month. What is bugger's projected cash balance at the end of May 2008?

Answer per the key - $301,000. I can't get to it. Please see the spreadsheet sent via email.

Thanks

Explanation / Answer

This one is a little tricky. I sent the excel spreadsheet back. Your modeling of COGS is correct. The receipts schedule you built was also correct. You just need to make sure that purchases of COGS were correct on the timeline (one period after the purchase was made.)

Also for the cash account we set up a BoP amount, add the flow from the cash flow generated from operations, then make sure that the EoP is above the minimum cash balance of 20k. If not, we would have to make sure we issue short term debt to fill that gap. I have linked the spreadsheet correctly so you fiddle with it to see what might happen to the cash, debt accounts and the effect on interest expense should the company issue debt.

 

So our CFs will be (469,000-295,000) in March, (399,000-365,000) in April and (449,000-386,000) in May. Because all of these are positive and we start with 30,000 we never dip below 20k and do not need to issue new debt.

Thus if we add these CFs to 30000 we end up with 301,000 by the end of the month of May.

Hope this helps!

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.