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$0February$0March$275,000April$275,000May$312,000June$417,000July$714,000August$611,000September$827,000October$500,000November$116,000December$0The 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,000April$240,000May$225,000June$287,000July$350,000August$415,000September$242,000October$214,000November$222,000Additional 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 with the use of this tamplate.

Cash Budget for 10 months from March to December Particulars March April May June July August…

Answered: Answer the following questions…

EBK CONTEMPORARY FINANCIAL MANAGEMENT

14th Edition

ISBN:9781337514835

Author:MOYER

Publisher:MOYER

Chapter4: Financial Planning And Forecasting

Section: Chapter Questions

Problem 5P

See similar textbooks

Related questions

Question

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 with the use of this tamplate.

Definition Definition Estimate of an organization's cash flow for a future period. A cash budget forecasts future cash receipts and payments from various sources for a fiscal year. A cash budget can be created once a month or once a week to determine the organization's cash position and ensure its performance in relation to the budget. It aids in determining whether the company has enough cash and cash equivalents to meet its operational needs in the future.

Expert Solution

Step 1

Cash Budget for 10 months from March to December
Particulars	March	April	May	June	July	August	September	October	November	December
Opening balance (A)	50000	60000	50000	50000	50000	118400	50000	50000	50000	50000

Collections from Sales (see Working Note)	55000	220000	282400	325600	455400	83400	142800	122200	165400	100000
Total receipts (B)	55000	220000	282400	325600	455400	83400	142800	122200	165400	100000

Payment for Raw Material & Labour	0	220000	240000	225000	287000	350000	415000	242000	214000	222000
Adminstrative Salaries	30000	30000	30000	30000	30000	30000	30000	30000	30000	30000
Lease Payments	10000	10000	10000	10000	10000	10000	10000	10000	10000	10000
One Time Plant Cost	-	-	-	23000	-	-	-	-	-	-
Income Tax Payments	-	-	-	50000	-	-	50000	-	-	-
Miscellaneous Cost	5000	5000	5000	5000	5000	5000	5000	5000	5000	5000
Total Payments (C)	45000	265000	285000	343000	332000	395000	510000	287000	259000	267000

Net Cash balance (A+B-C)	60000	15000	47400	32600	173400	-193200	-317200	-114800	-43600	-117000
Add: Borrowings to maintain min Bal	0	35000	2600	17400	0	243200	367200	164800	93600	167000
Less: Paid back	0	0	0	0	55000	0	0	0	0	0
Closing Balance	60000	50000	50000	50000	118400	50000	50000	50000	50000	50000