4 5 Prepare a cash budget for Green Corp. using the following assumptions: 6 7 9 Collections related to all sales have been projected to be: 20% collected during the month of sale 60% collected one month after sale 20% collected two months after sale 10 11 12 13 Expenses are projected to be as follows: 15 16 17 18 19 20 May-20 $45,000 35,000 Jun-20 $49,050 32,900 Sales - Product A Sales - Product B Rent Labor Taxes Depreciation Overhead Equipment purchases Miscellaneous 21 22 Projected Monthly +/- 9.0% -6.0% 0.0% 2.0% 1.0% 0.0% 5.0% 1.0% 2.0% Jul-20 $53,465 30,926 22,000 25,000 10,000 1,000 10,000 15,000 3,000 23 24 25 26 27 28 29 Additional general information is as follows: All expenses are paid during the month incurred. Cash on hand at July 1, 2020 is projected to be $25,000. A minimum cash balance of $25,000 should be maintained throughout the cash budget period (the amounts needed to maintain that balance will be borrowed if necessary). 30 31 33 34 35 36 Required - Part 1: Prepare a monthly cash budget for the last six months of 2020 (July - December). How much money will Green Corp. have to borrow (or how much will it have available to invest) each month? Hint -- use a "=IF0" function to calculate the loan requirement and surplus for each month. Create your analysis in an Excel Worksheet Tab Cash Flow Proj Part 1 37 38 39