# Ch 20-12 Build a Model

### Pages to are hidden for

"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%

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 14 posted: 11/23/2011 language: English pages: 3