"Ch 20-12 Build a Model"
Ch 20-12 Build a Model 3/9/2001 Chapter 20. Partial Model for Ch 20-12 Build a Model Note: Fill in the shaded cells with the appropriate formulas This spreadsheet reworks Problem 20-11 and then goes on to ask and answer an additional question. Input Data Collections during month of sale 10% Collections during month after sale 75% Note: When the percent collected Collections during second month after sale 15% during the second month after sale Purchases as a % of next month's sales 70% is changed, the percent for Lease payments $9,000 collections during month after sale Target cash balance $90,000 is automatically changed so that General and administrative salaries $27,000 100% of sales are collected during Depreciation charges $36,000 the three-month period. Income tax payments (Sep & Dec) $63,000 Miscellaneous expenses $2,700 New design studio payment $180,000 Cash on hand July 1 $132,000 Minimum cash balance $90,000 Sales adjustment factor 0% May June July August September October November December January The Cash Budget $180,000 $180,000 $360,000 $540,000 $720,000 $360,000 $360,000 $90,000 $180,000 Collections and purchases worksheet Sales (gross) Collections During month of sale During 1st 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 each month to maintain the target cash balance? c. Would the cash budget be accurate if inflows came in all during the month but outflows were bunched early in the month? d. If the company produces on a seasonal basis. How would this affect the current ratio and the debt ratio? e. (New question) If its customers began to pay late, this would slow down collections and thus increase the required loan amount. Also, if sales dropped off, this would have an effect on the required loan. Do a sensitivity analysis that shows the effects of these two factors on the max loan requirement. Change Maximum Loan Required in Sales % Collections in 2nd month $0 0% 15% 30% 45% 60% 75% 90% -100% -75% -50% -25% 0% 25% 50% 75% 100%