Cash budgets are essential to all small businesses. In this case, middletown construction company has estimated the following sales forecast in the next 12 months. January $0 February $0 March $275,000 April $275,000 May $312,000 June $417,000 July $714,000 August $611,000 September $827,000 October $500,000 November $116,000 December $0 The company also gathered the following collection estimates regarding the forecast sales: Payment collection within the month of sale = 20% Payment collection the month following sales = 60% Payment collection the second month following sales = 20% Payments for raw materials and labor costs are paid the month after the purchase and are estimated as follows: March $220,000 April $240,000 May $225,000 June $287,000 July $350,000 August $415,000 September $242,000 October $214,000 November $222,000 Additional financial information is as follows: Administrative salaries will approximately amount to $30,000 a month. Lease payments are around $10,000 a month. A one-time new plant investment for $23,000 is expected to incur and be paid in June. Income tax payments estimated to be around $50,000 will be due in both June and September. Miscellaneous costs are estimated to be around $5,000 a month. Cash on hand on March 1 will be around $50,000, and a minimum cash balance of $50,000 shall be on hand at all times. Answer the following questions including all the formulas and calculations to arrive at financial values. 1. Use Excel to prepare the monthly cash budget of the company. a) calculate total cash inflow for each month? b) Calculate cash outflow for each month 2. Prepare a monthly cash budget for Middletown Construction, Inc. for the 12-month period of January through December. 3. Will the company need any outside financing? 4. When is the line of credit the highest for MCI will need? 5. If you were a bank manager, would you want MCI as your client? Why or why not?
Master Budget
A master budget can be defined as an estimation of the revenue earned or expenses incurred over a specified period of time in the future and it is generally prepared on a periodic basis which can be either monthly, quarterly, half-yearly, or annually. It helps a business, an organization, or even an individual to manage the money effectively. A budget also helps in monitoring the performance of the people in the organization and helps in better decision-making.
Sales Budget and Selling
A budget is a financial plan designed by an undertaking for a definite period in future which acts as a major contributor towards enhancing the financial success of the business undertaking. The budget generally takes into account both current and future income and expenses.
Cash budgets are essential to all small businesses. In this case, middletown construction company has estimated the following sales
| January | $0 |
| February | $0 |
| March | $275,000 |
| April | $275,000 |
| May | $312,000 |
| June | $417,000 |
| July | $714,000 |
| August | $611,000 |
| September | $827,000 |
| October | $500,000 |
| November | $116,000 |
| December | $0 |
The company also gathered the following collection estimates regarding the forecast sales:
- Payment collection within the month of sale = 20%
- Payment collection the month following sales = 60%
- Payment collection the second month following sales = 20%
Payments for raw materials and labor costs are paid the month after the purchase and are estimated as follows:
| March | $220,000 |
| April | $240,000 |
| May | $225,000 |
| June | $287,000 |
| July | $350,000 |
| August | $415,000 |
| September | $242,000 |
| October | $214,000 |
| November | $222,000 |
Additional financial information is as follows:
- Administrative salaries will approximately amount to $30,000 a month.
- Lease payments are around $10,000 a month.
- A one-time new plant investment for $23,000 is expected to incur and be paid in June.
- Income tax payments estimated to be around $50,000 will be due in both June and September.
- Miscellaneous costs are estimated to be around $5,000 a month.
- Cash on hand on March 1 will be around $50,000, and a minimum cash balance of $50,000 shall be on hand at all times.
Answer the following questions including all the formulas and calculations to arrive at financial values.
1. Use Excel to prepare the monthly
a) calculate total
b) Calculate
2. Prepare a monthly cash budget for Middletown Construction, Inc. for the 12-month period of January through December.
3. Will the company need any outside financing?
4. When is the line of credit the highest for MCI will need?
5. If you were a bank manager, would you want MCI as your client? Why or why not?
“Since you have posted a question with multiple sub parts, we will provide the solution only to the first three sub parts as per our Q&A guidelines. Please repost the remaining sub parts separately"
Cash Budget
A cash budget is a forecast of a company's cash flows over a given time frame. The budget in question can be for a week, month, quarter, or year. This plan is used to determine whether the organization has enough money to keep running for the specified period of time. The cash budget aids in determining an effective cash allocation by giving a business knowledge into its cash requirements (as well as any excess).
Trending now
This is a popular solution!
Step by step
Solved in 5 steps with 6 images
