2 Prepare a cash budget for Data Logic using the following assumptions. Data Logic produces two products, widgets and gadgets. May 2012 sales projection for widgets: Monthly sales increase for widgets: May 2012 sales projection for gadgets Monthly sales decrease for gadgets 3 Collections on total sales have been projected to be: 4 15 16 17 $45,000 12% $35,000 -10% 20% collected during the month of sale 60% collected one month after sale 20% collected two months after sale 18 Expenses are projected to be as follows: 19 20 21 Rent 22 Labor 23 Taxes 24 Depreciation 25 Overhead 26 Equipment purchase 27 Miscellaneous May-12 Monthly increase 20.000 25.000 10.000 1,000 10.000 5% 15.000 1% 3.000 ***** 0% 2% 1% 0% All expenses are paid during the month incurred, except rent and labor Rent and labor will be paid the following month. Cash on hand on July 1, 2012 is projected to be $25,000 A minimum cash balance of $25,000 should be maintained through the cash budget period Prepare a monthly cash budget for the last six months of 2012. How much money will Data Logic need to borrow (or how much will it have available to invest) each month? Use the IF function to calculate the loan requirement and surplus for each month. Save Excel file as cashflow! Prepare a monthly cash budget for the last six months of 2012 Change the following 9 assumptions 1 Collections on sales have been projected to be 12 13 44 45 46 35% collected during the month of sale 55% collected one month after sale 5% collected two months after sale 5% will be uncollectable 47 How much money will Data Logic need to borrow (or how much will it have available to invest) 48 each month? Use the IF function to calculate the loan requirement and surplus for each month 49 Save Excel file as cashflow2 50