e. If its customers began to pay late, this would slow down collections and thus increase the required loan amount. Also, if sales dropped off, this would have an effect on the required loan. Do a sensitivity analysis that shows the effects of these two factors on the max loan requirement. Assume the purchases of labor and raw material also vary by the sales adjustment factor. Change in Max Loan Pa ment Sales %Collection in 2nd Month $74,000 30% 45% 60% 75% 90% Answer: ___ Input Data Collections during month of sale 20% Collections during month after sale 60% Note: When the percent collected during Collections during second month after sale 25% the second month after sale is changed, Lease payments $7,000 the percent for collections during month $30,000 after sale is automatically changed so Target cash balance $25,000 that 100% of sales are collected during General and administrative salaries the three-month period Depreciation charges $8,00 Income tax payments (Sep & Dec) $30,000 Miscellaneous expenses $5,000 New office suite payment (Oct) $95,000 Cash on hand July 1 $70,000 Sales, labor, and RM adjustment factor 0% a. Prepare a monthly cash budget for the last six months of the year. May June July August September October November December January Original sales estimates $75,000 $115,000 $145,000 $125,000 $120,000 $95,000 $75,000 $55,000 $45,000 Original labor and raw mat. estimates $80,000 $75,000 $105,000 $85,000 $65,000 $70,000 $30,000 $35,000 Forecasted Sales Sales (gross) $75,000 $115,000 $145,000 $125,000 $120,000 $95,000 $75,000 $55,000 $45,000 Collections During month of sale $29,000 $25,000 $24,000 $19,000 $15,000 $11,000 During 1st month after sale $69,000 $87,000 $75,000 $72,000 $57,000 $45,000 During 2nd month after sale $15,000 $28,750 $36,250 $31,250 $30,000 $23,750 Total collections $113,000 $140,750 $135,250 $122,250 $102,000 $79,750 Purchases Labor and raw materials $80,000 $75,000 $105,000 $85,000 $65,000 $70,000 $30,000 $35,000 Payments for labor and raw materials $75,000 $105,000 $85,000 $65,000 $70,000 $30,000 Payments Payments for labor and raw materials 75,000 105,000 85,000 65,000 70,000 30,000 General and administrative salaries 25,000 25.000 25,000 25.000 25.000 25,000 Lease payments 7,000 7,000 7,000 7,000 7,000 7,000 Miscellaneous expenses 5,000 5,000 5,00 5,000 5,000 5,000 Income tax payments 30,000 30,000 Design studio payment 95,000 Total payments $112,000 $142,000 $152,000 $197,000 $107,000 $97,000 Net Cash Flows Cash on hand at start of forecast period $70,000 $71,000 $69,750 $53,000 ($21,750) ($26,750) Net cash flow (NCF): Total collections - Total payments $1,000 ($1,250 ($16,750) ($74,750) ($5,000) ($17,250) Cumulative NCF: Prior month cumulative + this month's NCF $71,000 $69,750 $53,000 ($21,750) ($26,750) ($44,000) Cash Surplus (or Loan Requirement) Target cash balance $30,000 $30,000 $30,000 $30,000 $30,000 $30,000 Surplus cash or loan needed: Cum NCF - Target cash $41,000 $39,750 $23,000 ($51,750) ($56,750) ($74,000) Max. Loan $74,000