Chapter 16 Cash Budget Concept Input Data Collections during month of sale Collections during month after sale Collections during second month after sale Lease payments Target cash balance General and administrative salaries Depreciation charges Income tax payments (March \& June) Miscellaneous expenses New plant facility payment (April) Cash on hand January 1 st NO CASH DISCOUNT IS OFFERED IN THE MONTH OF SALE a. Prepare a monthly cash budget for the first six months of the 2023 Original sales estimates Original labor and raw mat. estimates \begin{tabular}{|r|r|r|r|r|r|r|r|} \hline November & December & January & February & March & April & May & \multicolumn{1}{|c|}{ June } \\ \hline $150,000 & $175,000 & $375,000 & $755,000 & $825,500 & $350,000 & $390,000 & $90,000 \\ \hline $90,000 & $99,000 & $224,000 & $885,000 & $346,500 & $236,000 & $85,000 & $115,000 \\ \hline \end{tabular} Forecasted Sales Sales (gross) \begin{tabular}{llllllll} $60,000 & $76,000 & $151,000 & $130,000 & $479,000 & $114,000 & $305,000 & $25,000 \\ \hline \end{tabular} Sales (gross) Original sales estimate Collections During month of sale During 1 st month after sale During 2 nd month after sale Total collections Purchases Labor and raw materials Payments for labor and raw materials Payments Payments this month are for last months Labor and raw Payments for labor and raw materials materials. General and administrative salaries Lease payments Miscellaneous expenses Income tax payments New plant facility payment Total payments Cash on Hand January 1st can ONLY be used for Met Cash Flows January Cash at start of month Net cash flow (NCF): Total collections - Total payments Cumulative NCF: Prior month cumulative + this month's NCF Net Cash Flows January Cash at start of month Net cash flow (NCF): Total collections - Total payments Cumulative NCF: Prior month cumulative + this month's NCF Cash Surplus (or Loan Requirement) Target cash balance Surplus cash or loan needed: Cum NCF - Target cash \begin{tabular}{|l|l|l|} \hline Use Excels MIN function for row 55 with a minus sign in front of MIN & & \\ \hline & Highest Loan Needed \\ \hline \end{tabular}