This spreadsheet develops a cash budget as required for Problem 17-10, Part a. It then goes on to ask and answer a few additional questions. Input Data Assumed constant. Don'change. Formula. Don't change. Allow this value to change to reflect slower collections. Collections during month of sale Collections during month after sale Collections during second month after sale Lease payments General and administrative salaries Depreciation charges Income tax payments (Sep & Dec) Miscellaneous expenses New design studio payment Cash on hand July 1 Minimum cash balance Sales adjustment factor Sales $180,000 Labor & raw materials $90,000 10% 75% 15% $9,000 $27,000 S36,000 S63,000 $2,700 $180,000 S132,000 $90,000 0% $180,000 $180,000 $90,000 S360,000 $126,000 $540,000 $882,000 $720,000 $306,000 $360,000 $234,000 $360,000 $162,000 $90,000 $90,000 July August September October November December January a. Prepare a monthly cash budget for the last 6 months of 2009. The Cash Budget May June Collections and purchases worksheer Sales (gross) Collections During month of sale During Ist month after sale During 2nd month after sale Total collections Purchases Labor and raw materials Payments for labor and raw materials Cash gain or loss for month Collections Payments for labor and raw materials General and administrative salaries Lease payments Miscellaneous expenses Income tax payments Design studio payment Total payments Net cash gain (loss) during month Loan requirement or cash surplus Cash at start of month Cumulative cash Target cash balance Cumulative surplus cash or loans outstanding to maintain $90,000 target cash balance Max. Loan b. How much must Bowers borrow or will have to invest each month to maintain the target cash balance? Answer: c. Would the cash budget be accurate ir inflows came in all during the month but outflows were bunched early in the month? Answer: d. The company produces on a seasonal basis. How would this affect the current ratio and the debt ratio? Answer: Just before the busy season, the company would have some current assets, but not very much, and it e. (New question) Ir Bowers' customers began to pay late, collections would slow down, thus increasing the required loan amount. If sales declined, this also would have an effect on the required loan. Doa sensitivity analysis that shows the effects of these two factors on the maximum loan requirement. Answer: The "Sales adjustment factor" can be used to cause sales to vary from the base levels. Similarly, we can change the percentage of late paying customers. Here is the relevant data table: Change in Sales Maximum Loan Required % Collections in 2nd month 30% 45% 60% SO 15% 7 -100% -75% -50% -25% 0% 25% 50% 100% You can see from the table that, from the base case (collections = 15%, change in sales = 0), an increase in late payers increases the loan requirement, as does a decline in sales. This spreadsheet develops a cash budget as required for Problem 17-10, Part a. It then goes on to ask and answer a few additional questions. Input Data Assumed constant. Don'change. Formula. Don't change. Allow this value to change to reflect slower collections. Collections during month of sale Collections during month after sale Collections during second month after sale Lease payments General and administrative salaries Depreciation charges Income tax payments (Sep & Dec) Miscellaneous expenses New design studio payment Cash on hand July 1 Minimum cash balance Sales adjustment factor Sales $180,000 Labor & raw materials $90,000 10% 75% 15% $9,000 $27,000 S36,000 S63,000 $2,700 $180,000 S132,000 $90,000 0% $180,000 $180,000 $90,000 S360,000 $126,000 $540,000 $882,000 $720,000 $306,000 $360,000 $234,000 $360,000 $162,000 $90,000 $90,000 July August September October November December January a. Prepare a monthly cash budget for the last 6 months of 2009. The Cash Budget May June Collections and purchases worksheer Sales (gross) Collections During month of sale During Ist month after sale During 2nd month after sale Total collections Purchases Labor and raw materials Payments for labor and raw materials Cash gain or loss for month Collections Payments for labor and raw materials General and administrative salaries Lease payments Miscellaneous expenses Income tax payments Design studio payment Total payments Net cash gain (loss) during month Loan requirement or cash surplus Cash at start of month Cumulative cash Target cash balance Cumulative surplus cash or loans outstanding to maintain $90,000 target cash balance Max. Loan b. How much must Bowers borrow or will have to invest each month to maintain the target cash balance? Answer: c. Would the cash budget be accurate ir inflows came in all during the month but outflows were bunched early in the month? Answer: d. The company produces on a seasonal basis. How would this affect the current ratio and the debt ratio? Answer: Just before the busy season, the company would have some current assets, but not very much, and it e. (New question) Ir Bowers' customers began to pay late, collections would slow down, thus increasing the required loan amount. If sales declined, this also would have an effect on the required loan. Doa sensitivity analysis that shows the effects of these two factors on the maximum loan requirement. Answer: The "Sales adjustment factor" can be used to cause sales to vary from the base levels. Similarly, we can change the percentage of late paying customers. Here is the relevant data table: Change in Sales Maximum Loan Required % Collections in 2nd month 30% 45% 60% SO 15% 7 -100% -75% -50% -25% 0% 25% 50% 100% You can see from the table that, from the base case (collections = 15%, change in sales = 0), an increase in late payers increases the loan requirement, as does a decline in sales