The goal is to figure out what the Cash Balance needed each month over a 3 month spread.
1st write down all the given information:
Sales for all three months will be $130,000
Raw Material Purchases is 70% of $130,000
Accounts Receivable is 40% of $130,000
Some customers will pay within the same month and take advantage of the 3% Discount.
Net the Accounts Receivable amount with the 3% Discount to get the Accounts Receivable paid within the month.
Then there are Cash Disbursements for Accounts Payable, Wages Payable, and Rent.
Accounts Payable and Wages Payable will be a % of Sales of $130,000 - percentages are given in the problem
Rent Expenses is fixed over the 3 months, $15,000 each month
The organization does not want its bank balance to be below $6,000
2nd use the Excel template attached to document your work:
The information in this spreadsheet may not be accurate. This is an example of the same problem, however, a different version of a Corporate Finance textbook, and the amounts and percentages are slightly different.
You should come out with a Shortfall rather than a Surplus. I say this because when calculating the figures you might think something is inaccurate. This company is trying to create its Cash Flow Forecast over a 3 month period with actual transaction activity the problem provided.
3rd since this organization already knows it is going to be short meeting its Cash Needs over the 3 month period, explain how the organization can come up with a solution to have positive cash flow. If this were your organization and you could not meet the bills/obligations, how would you remedy this situation?
Attachment:- Template.xlsx