Helen Bowers, owner of Helens Fashion Designs, is planning to request a line of credit from her bank. In order to support her request, she has asked you to prepare a cash budget for her business for the second half of 2021 (July December). She has provided the following monthly forecasts for Sales, and for labor and raw material expenses:

May June July August September October November December Jan 2022

Sales

180,000 180,000 360,000 540,000 720,000 360,000 360,000 90,000 180,000

Labor and Raw Materials

90,000 90,000 126,000 882,000 306,000 234,000 162,000 90,000

In addition, Bowers has provided the following information: Collection of receivables: 20% within 1 month of sale, 65% first month (30-60 days), and 15% second month (60-90 days). Payments for labor and raw materials are made the month after services are provided (between 30 and 60 days) General and administrative salaries are $27,000/month Lease payments are $9,000/month Depreciation charges are $36,000/month Miscellaneous expenses are $2,700/month Quarterly income tax payments of $63,000 are due in September and December A progress payment of $180,000 on a new design studio must be paid in October 2021 Bowers expects to have a cash balance of $132,000 on July 1 (the beginning of the budget period). The company intends to maintain a minimum cash balance of $90,000 throughout the cash budget period.

With excel math please!

Helen's Fashion Designs Cash Budget-03-04 2021 Assumptions Collections during month of sale Collections during 1st month after sale Collections during and month after sale Percent bad debts Discount on first month collections General & Administrative Salaries Lease Payments Miscellaneous Expenses Income Tax payment Studio progress payment Beginning Cash Balance Target cash balance May Jung July August September October November December Jan 2022 Sales initial assumption Labor and Raw Materials - initial assumption Cash Budget 2022 2021 June May July August September October November December January Salus gros Collections During month of sale During 1st month after sale During 2nd month after sale Total Collections Labor and Raw materials Payments Payment for materials last month's purchases Gen & Admin Salaries Lease payments Miscellaneous expenses Taxes Payment for new studio Total Payments Net Cash Flows NCF for month Cash Balance Cash Surplus for Loan Required) Target Cash Balance Surplus Cash for loan needed) Maximum required loan Maximum available for investment Scenario Analysis: Imllement the following assumptions, and paste the Surplus Cash values for July-December into the shaded areas. Scenarios should be independent (not all at once) Scenario 1 Assume that 5% of debts are uncollectable 20% month of sale July August September October November December 70% first month Surplus Cash (or loan needed) 5% second month Scenario 2 Assume that collections slow dow: 10% month of sale July August September October November December Helen's Fashion Designs Cash Budget-03-04 2021 Assumptions Collections during month of sale Collections during 1st month after sale Collections during and month after sale Percent bad debts Discount on first month collections General & Administrative Salaries Lease Payments Miscellaneous Expenses Income Tax payment Studio progress payment Beginning Cash Balance Target cash balance May Jung July August September October November December Jan 2022 Sales initial assumption Labor and Raw Materials - initial assumption Cash Budget 2022 2021 June May July August September October November December January Salus gros Collections During month of sale During 1st month after sale During 2nd month after sale Total Collections Labor and Raw materials Payments Payment for materials last month's purchases Gen & Admin Salaries Lease payments Miscellaneous expenses Taxes Payment for new studio Total Payments Net Cash Flows NCF for month Cash Balance Cash Surplus for Loan Required) Target Cash Balance Surplus Cash for loan needed) Maximum required loan Maximum available for investment Scenario Analysis: Imllement the following assumptions, and paste the Surplus Cash values for July-December into the shaded areas. Scenarios should be independent (not all at once) Scenario 1 Assume that 5% of debts are uncollectable 20% month of sale July August September October November December 70% first month Surplus Cash (or loan needed) 5% second month Scenario 2 Assume that collections slow dow: 10% month of sale July August September October November December