In addition, Bowers has provided the folfowing information: - Collection of receivables: 20% within 1 month of sale, 65% first month ( 3060 days), and 15% second month ( 6090 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. 1. Prepare a monthly cash budget for the last 6 months of 2021 using the template provided. Be sure to place all of the starting assumptions in the indicated cells in rows 825. The values in rows 2957 should ALL be calculated using formulas... no credit will be given for hardcoded values in this range! 2. Prepare monthly estimates of the amount of financing required or excess funds available. While Helen's Fashions is profitable, its business is highly seasonal. How large of a credit line is needed for the firm to remain solvent? 7 Assumptions 3 Collections during month of sale 7) Collections during 1st month after sale 4. Collections during 2nd month atter sale (1) Percent bad debts 12. Discount on first month collections 13 General \& Administrative Salaries 14 Lease Payments 15 Miscellaneous Expenses Income Tax payment Studio progress payment. Beginning Cash Balance Target cash balance Sales - initial assumption Labor and Raw Materials - initial assumption Cash Budget Sales (or0ss) Coliections During mans ef sale During ist erenth after sale Duting 2od month ater sen foral Colections Labor and Raw materais Paymentit Pajmest for materias last month' purchases Gen 8 Mdmin Salanes Lease parmenis Meselaneout exienses Taxes Payment tor new shusio Total Paymenta Net Cash Flows NCF for month Cash Balance Cash Surplus (or Loan Rocured) Target Cash Balance Surplus Cash (or loan needed) Maximum required loan Maximum avalable for investment Scenario Analysis: Imilioment the following assumptions, and pasie the Surplus Cash values for July-December into the shaded areas. Scenarios should be independent (not all at ond 45. Scenario 1: 6) Assume that 5% of debts are uncollectable 4) 20% month of sale 41. Ta\% fint month Surplus Cash (or loan needed) July August September October November December 5% seoond morth Scenario 2: 4) Assume that collections slow down: Scenario 2: Assume that collections slow down: 10% month of sale 50% first month 40% second month Surplus Cash (or loan needed) \begin{tabular}{|llll} July August September October November De. \end{tabular} . Sor Scenario 3: Offer 2% discount for immediate payment 40% month of sale, with discount 50% first month 10% second month Surplus Cash (ortoan needed) July August September October November Dece Scenario 4: Stretch payables: pay for purchases 2 months prior Surplus Cash (or loan needed) July August September October November Decen 3. Adjust your model to reflect the four alternative scenarios given below: a. Assume that 5% of debts are uncollectable. You can just reduce the second-month collections from 15% to 10% to accomplish this. b. Assume that collections slow down: 10% within 1 month of sale, 50% first month ( 3060 days), and 40% second month ( 6090 days). c. Assume that you offer a 2% discount for customers who pay immediately. Adjust the collections to: 40% within 1 month of sale, 50% first month (30-60 days), and 10% second month ( 6090 days). Don't forget to reduce the month-of-sale collections by 2% to reflect the discount! d. Assume that you are able to stretch the accounts payable: instead of paying for purchases after one month, account for payment in the second month. (Payments should reflect purchases 2 months prior rather than one month prior.) Treat each of these scenarios as independent... implement each one in isolation, not all at once. After making the changes, copy and paste the values in the Surplus Cash line (row 53) into the appropriate yellow-shaded cells. In addition, Bowers has provided the folfowing information: - Collection of receivables: 20% within 1 month of sale, 65% first month ( 3060 days), and 15% second month ( 6090 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. 1. Prepare a monthly cash budget for the last 6 months of 2021 using the template provided. Be sure to place all of the starting assumptions in the indicated cells in rows 825. The values in rows 2957 should ALL be calculated using formulas... no credit will be given for hardcoded values in this range! 2. Prepare monthly estimates of the amount of financing required or excess funds available. While Helen's Fashions is profitable, its business is highly seasonal. How large of a credit line is needed for the firm to remain solvent? 7 Assumptions 3 Collections during month of sale 7) Collections during 1st month after sale 4. Collections during 2nd month atter sale (1) Percent bad debts 12. Discount on first month collections 13 General \& Administrative Salaries 14 Lease Payments 15 Miscellaneous Expenses Income Tax payment Studio progress payment. Beginning Cash Balance Target cash balance Sales - initial assumption Labor and Raw Materials - initial assumption Cash Budget Sales (or0ss) Coliections During mans ef sale During ist erenth after sale Duting 2od month ater sen foral Colections Labor and Raw materais Paymentit Pajmest for materias last month' purchases Gen 8 Mdmin Salanes Lease parmenis Meselaneout exienses Taxes Payment tor new shusio Total Paymenta Net Cash Flows NCF for month Cash Balance Cash Surplus (or Loan Rocured) Target Cash Balance Surplus Cash (or loan needed) Maximum required loan Maximum avalable for investment Scenario Analysis: Imilioment the following assumptions, and pasie the Surplus Cash values for July-December into the shaded areas. Scenarios should be independent (not all at ond 45. Scenario 1: 6) Assume that 5% of debts are uncollectable 4) 20% month of sale 41. Ta\% fint month Surplus Cash (or loan needed) July August September October November December 5% seoond morth Scenario 2: 4) Assume that collections slow down: Scenario 2: Assume that collections slow down: 10% month of sale 50% first month 40% second month Surplus Cash (or loan needed) \begin{tabular}{|llll} July August September October November De. \end{tabular} . Sor Scenario 3: Offer 2% discount for immediate payment 40% month of sale, with discount 50% first month 10% second month Surplus Cash (ortoan needed) July August September October November Dece Scenario 4: Stretch payables: pay for purchases 2 months prior Surplus Cash (or loan needed) July August September October November Decen 3. Adjust your model to reflect the four alternative scenarios given below: a. Assume that 5% of debts are uncollectable. You can just reduce the second-month collections from 15% to 10% to accomplish this. b. Assume that collections slow down: 10% within 1 month of sale, 50% first month ( 3060 days), and 40% second month ( 6090 days). c. Assume that you offer a 2% discount for customers who pay immediately. Adjust the collections to: 40% within 1 month of sale, 50% first month (30-60 days), and 10% second month ( 6090 days). Don't forget to reduce the month-of-sale collections by 2% to reflect the discount! d. Assume that you are able to stretch the accounts payable: instead of paying for purchases after one month, account for payment in the second month. (Payments should reflect purchases 2 months prior rather than one month prior.) Treat each of these scenarios as independent... implement each one in isolation, not all at once. After making the changes, copy and paste the values in the Surplus Cash line (row 53) into the appropriate yellow-shaded cells