90.2K

Verified Solution

Link Copied!

Frepare a cash budget for ABC Company using the following assumptions: Collections related to all sales have been projected to be: 20% collected during the month of sale 60% collected one month after sale 20% colected two months after sale Expenses are projected to be as follows: Projected Monthly May-18 Jun-18 Jul-18 Sales - Product A $45,000 $49,050 $53,465 9.0% Sales - Product B 35,000 32,900 30 926 -6.0% Rent 22.000 0.0% Labor 25,000 20% Taxes 10,000 1.0% Depreciation 1,000 0.0% Overhead 10.000 5.0% Equipment purchases 15,000 1.0% Miscellaneous 3,000 2.0% Additional general information is as follows: All expenses are paid during the month incurred Cash on hand at July 1, 2018 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). Required - Part I: Prepare a monthly cash budget for the last six months of 2018 (July - December). How much money will ABC Company have to borrow (or how much will it have available to invest) each month? Use the "=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 Required - Part II: Create a copy of the analysis above in another worksheet tab. Prepare another monthly projected cash budget for the last six months of 2018 (July - December) Change the following assumptions: 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 How much money wil ABC Company need to borrow (or how much will it have available to invest) each month? Use the -IFO function to calculate the loan requirement and surplus for each month Create your analysis in an Excel Worksheet Tab Cash Flow Proj Part II Save the Excel file as Name_Cashflow Assignment3

Answer & Explanation Solved by verified expert

See Answer