Prepare a cash budget for Greshak Corp. using the following assumption: Collections related to all sales...

 

Transcribed Image Text:

Prepare a cash budget for Greshak Corp. using the following assumption: 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 Expenses are projected to be as follows: Sales - Product A Sales Product B Rent Labor Taxes Depreciation Overhead Equipment purchases Miscellaneous May-20 Jun-20 $45,000 $49,050 35,000 32,900 Jul-20 $53,465 30,926 22,000 25,000 10,000 1,000 10,000 15,000 3,000 Projected Monthly +/- Collections on sales have been projected to be: 35% collected during the month of sale 55% collected one month after sale 5% collected two months after sale 5% will be uncollectable 9.0% -6.0% 0.0% 2.0% 1.0% 0.0% 5.0% 1.0% 2.0% Additional general information is as follows: All expenses are paid during the month incurred. Cash on hand at July 1, 2020 is projected $25,000. A minimum cash balance of $25,000 should be maintained throughout the cash b period (the amounts needed to maintain that balance will be borrowed if necessary). Required - Part I: Prepare a monthly cash budget for the last six months of 2020 (July - December). How mu money will Greshak Corp. have to borrow (or how much will it have available to invest) eac Hint -- use a "=IF()" function to calculate the loan requirement and surplus for each month Create your analysis in an Excel Worksheet Tab Cash Flow Proj Part I Required - Part II: Create a copy of the analysis above in another worksheet tab. Prepare another monthly projec cash budget for the last six months of 2020 (July-December). Change the following assumpti How much money will Greshak Corp. need to borrow (or how much will it have available to inve month? Hint -- use a "=IF()" function to calculate the loan requirement and surplus for each m Create your analysis in an Excel Worksheet Tab Cash Flow Proj Part II Prepare a cash budget for Greshak Corp. using the following assumption: 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 Expenses are projected to be as follows: Sales - Product A Sales Product B Rent Labor Taxes Depreciation Overhead Equipment purchases Miscellaneous May-20 Jun-20 $45,000 $49,050 35,000 32,900 Jul-20 $53,465 30,926 22,000 25,000 10,000 1,000 10,000 15,000 3,000 Projected Monthly +/- Collections on sales have been projected to be: 35% collected during the month of sale 55% collected one month after sale 5% collected two months after sale 5% will be uncollectable 9.0% -6.0% 0.0% 2.0% 1.0% 0.0% 5.0% 1.0% 2.0% Additional general information is as follows: All expenses are paid during the month incurred. Cash on hand at July 1, 2020 is projected $25,000. A minimum cash balance of $25,000 should be maintained throughout the cash b period (the amounts needed to maintain that balance will be borrowed if necessary). Required - Part I: Prepare a monthly cash budget for the last six months of 2020 (July - December). How mu money will Greshak Corp. have to borrow (or how much will it have available to invest) eac Hint -- use a "=IF()" function to calculate the loan requirement and surplus for each month Create your analysis in an Excel Worksheet Tab Cash Flow Proj Part I Required - Part II: Create a copy of the analysis above in another worksheet tab. Prepare another monthly projec cash budget for the last six months of 2020 (July-December). Change the following assumpti How much money will Greshak Corp. need to borrow (or how much will it have available to inve month? Hint -- use a "=IF()" function to calculate the loan requirement and surplus for each m Create your analysis in an Excel Worksheet Tab Cash Flow Proj Part II