COULD YOU HELP ME JUST DOING THIS GRAPH PLEASE? THANK YOU

Excel Activity: Cash Budgeting Helen Bowers, owner of Helen's Fashion Designs, is planning to request a line of credit from her bank. She has estimated the following sales forecasts for the firm for parts of 2021 and 2022: May 2021 June July August September October November December January 2022 $160,000 160,000 320,000 480,000 640,000 320,000 320,000 80,000 160,000 Estimates regarding payments obtained from the credit department are as follows: collected within the month of sale, 10%; collected the month following the sale, 75%; collected the second month following the sale, 15%. Payments for labor and raw materials are made the month after these services were provided. Here are the estimated costs of labor plus raw materials: May 2021 June July August September October November December $ 80,000 80,000 112,000 784,000 272,000 192,000 128,000 80,000 General and administrative salaries are approximately $21,000 a month. Lease payments under long-term leases are $7,000 a month. Depreciation charges are $28,000 a month. Miscellaneous expenses are $3,150 a month. Income tax payments of $68,000 are due in September and December. A progress payment of $195,000 on a new design studio must be paid in October. Cash on hand on July 1 will be $124,000, and a minimum cash balance of $90,000 should be maintained throughout the cash budget period. The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round intermediate calculations. Round your answers to the nearest dollar. If your answer is zero, enter "O". Download spreadsheet Cash Budgeting-90531c.xlsx a. Prepare a monthly cash budget for the last 6 months of 2021. All payments and expenses should be entered as positive numbers. Net cash losses, negative cash balance, negative cumulative cash, and cumulative loans outstanding, if any, should be indicated by a minus sign. July August September October November $ 320000 $ 480000 $ 640000 $ 320000 $ 320000 $ Collections and purchases worksheet Sales (gross) Collections During month of sale During 1st month after sale During 2nd month after sale $ 32000 $ 32000$ 120000 $ 48000 $ 240000 $ 64000 $ 360000 480000 32000$ 240000 96000 368000 $ 24000 a 24000 a 48000 72000 584000 $ $ 176000 $ 312000$ 472000$ Total collections Purchases Labor and raw materials $ 112000 $ 784000 $ 272000 $ Numeric field 192000 $ 272000$ 128000 $ 192000 $ Payments for labor and raw materials $ 80000 $ 112000 $ 784000 $ $ 320000 $ 480000 $ 640000 $ 320000 $ $ 320000 $ Sales (gross) Collections During month of sale During 1st month after sale During 2nd month after sale $ 48000 $ 32000$ 32000$ 120000 24000 64000 $ 360000 32000 $ 240000 240000 M 480000 24000 48000 72000 96000 Total collections $ 176000 $ 312000$ 472000 $ 584000 $ 368000 $ Purchases Labor and raw materials $ 128000 $ 112000 $ 80000 $ 784000 $ 112000 $ 272000 $ 784000 $ 192000$ 272000$ Payments for labor and raw materials $ 192000$ 1 Cash gain or loss for month Collections Payments for labor and raw materials $ 312000$ 472000 $ 584000 $ 368000 $ $ 112000$ 784000 $ 272000$ 192000 $ General and administrative salaries 176000 $ 80000 $ 21000 7000 g 3150 21000 21000 21000 21000 7000 Lease payments 7000 7000 3150 g 7000 g 3150 Miscellaneous expenses 3150 Income tax payments 3150 og ou 68000 0 0 0 09 111150 $ o 195000 Design studio payment Total payments Net cash gain (loss) during month 0 $ 143150 $ 883150 $ 498150 $ 223150 $ $ 64850 $ 168850 $ -411150 $ 85850 $ 144850 $ Loan requirement or cash surplus Cash at start of month $ 188850 $ Cumulative cash $ 124000 $ 188850 $ 90000 357700$ 357700$ -53450 $ 90000 -53450 $ 32400$ 90000 g 32400 $ 177250 $ 90000 M Taraet cash balance 90000 g $ 320000 $ 480000 $ 640000 $ 320000 $ 320000 $ 80000 gross) ons month of sale 1st month after sale 2nd month after sale $ 32000 $ 64000 $ 360000 8000 240000 480000 32000$ 120000 24000 176000 y $ 48000 $ 240000 g 24000 312000$ 32000 y $ 240000 96000 48000 g 72000 48000 bllections $ 472000 $ 584000 y $ 368000 $ 296000 ses Ind raw materials $ 80000 112000$ 80000 $ 784000 $ 112000$ 272000 $ 784000 $ 192000$ 272000 y $ 128000 $ 192000$ hts for labor and raw materials $ 128000 pin or loss for month ons $ 312000$ 472000$ 584000 $ 296000 hts for labor and raw materials $ 784000 $ 272000 $ 176000 $ 80000 $ 21000 7000 3150 368000 $ 192000 $ 21000 I and administrative salaries 21000 21000 128000 21000 7000 payments 7000 7000 112000$ 21000 7000 3150 og og 7000 M 3150 aneous expenses 3150 3150 g 3150 g 68000 0 68000 M tax payments studio payment oo o 0 og 0 195000 0 $ 111150 $ 143150 $ 883150 $ 498150 $ 223150 y $ 227150 pyments h gain (loss) during month $ 64850 y $ 168850 $ -411150 $ 85850 $ 144850 $ 68850 quirement or cash surplus start of month $ -53450 $ 177250 124000 $ 188850 y $ ative cash $ 188850 $ 357700 $ 90000 357700 $ -53450 $ 90000 $ 32400 y $ 177250 $ 90000 32400 90000 246100 cash balance 90000 90000 equirement or cash surplus t start of month $ 124000 $ 188850 $ -53450 $ 177250 $ $ 32400 $ 357700 $$ -53450 $ 90000 g 188850 $ 90000 357700 90000 246100 32400 $ 177250 $ 90000 90000 90000 ative cash cash balance ative surplus cash or loans nding to maintain $90,000 cash balance $ 98850 $ 267700 $ -143450 $ -57600 $ 87250 $ 156100 e monthly estimates of the required financing or excess funds-that is, the amount of money Bowers will need to borrow or will have available st. Required financing, if any, should be indicated by a minus sign. Required financing or excess funds $ 98850 $ 267700 M ber $ -143450 $ -57600 er ber $ 87250 ber $ 156100 M uppose receipts from sales come in uniformly during the month (that is, cash receipts come in at the rate of 1/30 or 1/31 each day), but all is must be paid on the 5th. Will this affect the cash budget? That is, will the cash budget you prepared be valid under these assumptions? If hat could be done to make a valid estimate of the peak financing requirements? No calculations are required, although if you prefer, you can culations to illustrate the effects. uation, where inflows and outflows are not synchronized during the month, it is not likely to be possible to use cash budget e. If Bowers' customers began to pay late, collections would slow down, thus increasing the required loan amount. If sales declined, this also would have an effect on the required loan. Do a sensitivity analysis that shows the effects of these two factors on the maximum loan requirement. Enter your answers as positive numbers. . To complete the sensitivity analysis, follow these steps in excel: Ensure that cell A60 is a reference to cell 056 (i.e. "=B56"). o Select/highlight cells A60 through H69 (A60:H69). o From the top ribbon, select Data > Forecast > What-if-Analysis > Data Table o For row input cell click on cell B5 or enter $B$5. For column input cell click on cell B14 or manually enter $B$14. o Click "OK". % Collections in 2nd month Change in sales 0% 15% 30% 45% 60% 75% 90 $ $ $ $ $ $ $ -100% -75% $ $ $ $ $ $ $ -50% $ $ $ $ $ $ $ -25% $ $ $ $ $ $ $ 0% $ $ 143450 $ $ $ $ $ 25% $ $ $ $ $ $ $ $ $ 50% $ $ $ $ $ $ 75% $ $ $ 0 0 $ $ og $ $ 0 $ $ 0$ $ 0 og $ 100% $ og $ Excel Activity: Cash Budgeting Helen Bowers, owner of Helen's Fashion Designs, is planning to request a line of credit from her bank. She has estimated the following sales forecasts for the firm for parts of 2021 and 2022: May 2021 June July August September October November December January 2022 $160,000 160,000 320,000 480,000 640,000 320,000 320,000 80,000 160,000 Estimates regarding payments obtained from the credit department are as follows: collected within the month of sale, 10%; collected the month following the sale, 75%; collected the second month following the sale, 15%. Payments for labor and raw materials are made the month after these services were provided. Here are the estimated costs of labor plus raw materials: May 2021 June July August September October November December $ 80,000 80,000 112,000 784,000 272,000 192,000 128,000 80,000 General and administrative salaries are approximately $21,000 a month. Lease payments under long-term leases are $7,000 a month. Depreciation charges are $28,000 a month. Miscellaneous expenses are $3,150 a month. Income tax payments of $68,000 are due in September and December. A progress payment of $195,000 on a new design studio must be paid in October. Cash on hand on July 1 will be $124,000, and a minimum cash balance of $90,000 should be maintained throughout the cash budget period. The data has been collected in the Microsoft Excel file below. Download the spreadsheet and perform the required analysis to answer the questions below. Do not round intermediate calculations. Round your answers to the nearest dollar. If your answer is zero, enter "O". Download spreadsheet Cash Budgeting-90531c.xlsx a. Prepare a monthly cash budget for the last 6 months of 2021. All payments and expenses should be entered as positive numbers. Net cash losses, negative cash balance, negative cumulative cash, and cumulative loans outstanding, if any, should be indicated by a minus sign. July August September October November $ 320000 $ 480000 $ 640000 $ 320000 $ 320000 $ Collections and purchases worksheet Sales (gross) Collections During month of sale During 1st month after sale During 2nd month after sale $ 32000 $ 32000$ 120000 $ 48000 $ 240000 $ 64000 $ 360000 480000 32000$ 240000 96000 368000 $ 24000 a 24000 a 48000 72000 584000 $ $ 176000 $ 312000$ 472000$ Total collections Purchases Labor and raw materials $ 112000 $ 784000 $ 272000 $ Numeric field 192000 $ 272000$ 128000 $ 192000 $ Payments for labor and raw materials $ 80000 $ 112000 $ 784000 $ $ 320000 $ 480000 $ 640000 $ 320000 $ $ 320000 $ Sales (gross) Collections During month of sale During 1st month after sale During 2nd month after sale $ 48000 $ 32000$ 32000$ 120000 24000 64000 $ 360000 32000 $ 240000 240000 M 480000 24000 48000 72000 96000 Total collections $ 176000 $ 312000$ 472000 $ 584000 $ 368000 $ Purchases Labor and raw materials $ 128000 $ 112000 $ 80000 $ 784000 $ 112000 $ 272000 $ 784000 $ 192000$ 272000$ Payments for labor and raw materials $ 192000$ 1 Cash gain or loss for month Collections Payments for labor and raw materials $ 312000$ 472000 $ 584000 $ 368000 $ $ 112000$ 784000 $ 272000$ 192000 $ General and administrative salaries 176000 $ 80000 $ 21000 7000 g 3150 21000 21000 21000 21000 7000 Lease payments 7000 7000 3150 g 7000 g 3150 Miscellaneous expenses 3150 Income tax payments 3150 og ou 68000 0 0 0 09 111150 $ o 195000 Design studio payment Total payments Net cash gain (loss) during month 0 $ 143150 $ 883150 $ 498150 $ 223150 $ $ 64850 $ 168850 $ -411150 $ 85850 $ 144850 $ Loan requirement or cash surplus Cash at start of month $ 188850 $ Cumulative cash $ 124000 $ 188850 $ 90000 357700$ 357700$ -53450 $ 90000 -53450 $ 32400$ 90000 g 32400 $ 177250 $ 90000 M Taraet cash balance 90000 g $ 320000 $ 480000 $ 640000 $ 320000 $ 320000 $ 80000 gross) ons month of sale 1st month after sale 2nd month after sale $ 32000 $ 64000 $ 360000 8000 240000 480000 32000$ 120000 24000 176000 y $ 48000 $ 240000 g 24000 312000$ 32000 y $ 240000 96000 48000 g 72000 48000 bllections $ 472000 $ 584000 y $ 368000 $ 296000 ses Ind raw materials $ 80000 112000$ 80000 $ 784000 $ 112000$ 272000 $ 784000 $ 192000$ 272000 y $ 128000 $ 192000$ hts for labor and raw materials $ 128000 pin or loss for month ons $ 312000$ 472000$ 584000 $ 296000 hts for labor and raw materials $ 784000 $ 272000 $ 176000 $ 80000 $ 21000 7000 3150 368000 $ 192000 $ 21000 I and administrative salaries 21000 21000 128000 21000 7000 payments 7000 7000 112000$ 21000 7000 3150 og og 7000 M 3150 aneous expenses 3150 3150 g 3150 g 68000 0 68000 M tax payments studio payment oo o 0 og 0 195000 0 $ 111150 $ 143150 $ 883150 $ 498150 $ 223150 y $ 227150 pyments h gain (loss) during month $ 64850 y $ 168850 $ -411150 $ 85850 $ 144850 $ 68850 quirement or cash surplus start of month $ -53450 $ 177250 124000 $ 188850 y $ ative cash $ 188850 $ 357700 $ 90000 357700 $ -53450 $ 90000 $ 32400 y $ 177250 $ 90000 32400 90000 246100 cash balance 90000 90000 equirement or cash surplus t start of month $ 124000 $ 188850 $ -53450 $ 177250 $ $ 32400 $ 357700 $$ -53450 $ 90000 g 188850 $ 90000 357700 90000 246100 32400 $ 177250 $ 90000 90000 90000 ative cash cash balance ative surplus cash or loans nding to maintain $90,000 cash balance $ 98850 $ 267700 $ -143450 $ -57600 $ 87250 $ 156100 e monthly estimates of the required financing or excess funds-that is, the amount of money Bowers will need to borrow or will have available st. Required financing, if any, should be indicated by a minus sign. Required financing or excess funds $ 98850 $ 267700 M ber $ -143450 $ -57600 er ber $ 87250 ber $ 156100 M uppose receipts from sales come in uniformly during the month (that is, cash receipts come in at the rate of 1/30 or 1/31 each day), but all is must be paid on the 5th. Will this affect the cash budget? That is, will the cash budget you prepared be valid under these assumptions? If hat could be done to make a valid estimate of the peak financing requirements? No calculations are required, although if you prefer, you can culations to illustrate the effects. uation, where inflows and outflows are not synchronized during the month, it is not likely to be possible to use cash budget e. If Bowers' customers began to pay late, collections would slow down, thus increasing the required loan amount. If sales declined, this also would have an effect on the required loan. Do a sensitivity analysis that shows the effects of these two factors on the maximum loan requirement. Enter your answers as positive numbers. . To complete the sensitivity analysis, follow these steps in excel: Ensure that cell A60 is a reference to cell 056 (i.e. "=B56"). o Select/highlight cells A60 through H69 (A60:H69). o From the top ribbon, select Data > Forecast > What-if-Analysis > Data Table o For row input cell click on cell B5 or enter $B$5. For column input cell click on cell B14 or manually enter $B$14. o Click "OK". % Collections in 2nd month Change in sales 0% 15% 30% 45% 60% 75% 90 $ $ $ $ $ $ $ -100% -75% $ $ $ $ $ $ $ -50% $ $ $ $ $ $ $ -25% $ $ $ $ $ $ $ 0% $ $ 143450 $ $ $ $ $ 25% $ $ $ $ $ $ $ $ $ 50% $ $ $ $ $ $ 75% $ $ $ 0 0 $ $ og $ $ 0 $ $ 0$ $ 0 og $ 100% $ og $