Cash Flow Homework Assignment 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: 545,000 12% $35.000 -10% Collections on total 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: May-12 Monthly increase 0% Rent Labor Taxes Depreciation Overhead Equipment purchase Miscellaneous 20,000 25,000 10,000 1,000 10,000 15,000 3.000 1% 0% 5% 1% 2% Al expenses are paid during the month incurred, except rent and labor. Rent and labor w 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 w Data Logic need to borrow (or how much w it have available to invest) each month? Use the F function to calculate the loan requirement and surplus for each month. Save Excel file as castikowi Prepare a monthly cash budget for the last six months of 2012. 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% w be uncollectable How much money wil Data Logic need to bomow (or how much w it have available to rest) each month? Use the F function to calculate the loan requirement and surplus for each month. Save Excel file as castikow2 Both Excel files must be submitted for grading NOTE: If you are tuming in a hard copy, make sure you print out both spreadsheets and both sets of formulas. Cash Flow Homework Assignment 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: 545,000 12% $35.000 -10% Collections on total 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: May-12 Monthly increase 0% Rent Labor Taxes Depreciation Overhead Equipment purchase Miscellaneous 20,000 25,000 10,000 1,000 10,000 15,000 3.000 1% 0% 5% 1% 2% Al expenses are paid during the month incurred, except rent and labor. Rent and labor w 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 w Data Logic need to borrow (or how much w it have available to invest) each month? Use the F function to calculate the loan requirement and surplus for each month. Save Excel file as castikowi Prepare a monthly cash budget for the last six months of 2012. 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% w be uncollectable How much money wil Data Logic need to bomow (or how much w it have available to rest) each month? Use the F function to calculate the loan requirement and surplus for each month. Save Excel file as castikow2 Both Excel files must be submitted for grading NOTE: If you are tuming in a hard copy, make sure you print out both spreadsheets and both sets of formulas