Question:
The Genesis Energy operations management team is now preparing to implement the operating expansion plan. Previously, the firm’s cash position did not pose a challenge. However, the planned foreign expansion requires Genesis Energy to have a reliable source of funds for both short-term and long-term needs.
One of Genesis Energy’s potential lenders tells the team that in order to be considered as a viable customer, Genesis Energy must prepare and submit a monthly cash budget for the current year and a quarterly budget for the subsequent year. The lender will review the cash budget and determine whether or not Genesis Energy can meet the loan repayment terms. Genesis Energy’s ability to repay the loan depends not only on sales and expenses but also on how quickly the company can collect payment from customers and how well it manages its supplier terms and other operating expenses. The Genesis Energy team members agreed that being fully prepared with factual data would allow them to maximize their position as well as negotiate favorable financing terms.
The Genesis Energy management team held a brainstorming session to chart a plan of action, which is detailed here.
Since this expansion is critical to Genesis Energy expanding into new overseas markets, the operations management team has been asked to prepare an executive summary with supporting details for Genesis Energy’s senior executives.
Working over a weekend, the management team developed realistic assumptions to construct a working capital budget.
Based on this information, do the following:
Download this Excel spreadsheet to view the company’s cash budget. You will calculate the company’s monthly cash budget for the forthcoming year and quarterly budget for the subsequent year using this information.
This is the spreadsheet:
Genesis Cash Budget ($000)
Monthly BudgetQuarterly Budget
DecJanFebMarchAprilMayJuneJulyAugSeptOctNovDecMarchJuneSeptDec
Cash Inflow
Sales (Reference only)300,000200,000350,000400,000500,000550,000700,000700,000650,000900,000850,000750,000500,000150,000190,0003,000,0002,400,000
Cash Collections on Sales
10% in month of sale
25% in first month after sale
35% in second month after sale
30% in third month after sale
Other Cash Receipts
Total Cash Inflow
Cash Outflows
Material Purchases (reference only)
Payment for Material Purchase
100% in month after purchase
Other Cash Payments
Other production cost 30%
of Material cost paid month
after Purchase
Selling and Marketing Expense
General and Adminstrative expenses
Interest Payment
Tax Payment
Dividend Payment
Total Cash Outlfows
Net Cash Gain/(Loss)
Cash Flow Summary
Cash Balance start of the month
Net Cash Gain/loss
Cash Balance at end of month
Minium cash Balance desired
Surplus cash (deficit)
External Financing Summary
External Financing Balance
at start of month
New Financing Required
(negative amount from cash
suplus (deficit)
External Financing Requirement
External Financing Balance
Please help with the listed calculations.
WhatsApp us