A B C D E F G H I J K L M
1 Entson cash balance simulation
2 All monetary values are in $1000s.
3 Input section
4 Distribution of monthly sales (normal)
5 Nov Dec Jan Feb Mar Apr May Jun Jul Range names used:
6 Mean 1500 1600 1800 1500 1900 2600 2400 1900 1300 RecFactors - B14:D14
7 St Dev 70 75 80 80 100 125 120 90 70 CostPct - B17
8 InitCash - B19
9 Monthly fixed cost 250 250 250 250 250 250 MinCashBal - B20
10 Tax, dividend expenses 0 0 150 0 0 100 IntRateLoan - B23
IntRateCash - B24
11
BaseLev - B26
12 Receipts in any month are of form: A*(sales from 2 months ago)+B*(previous month's sales)+C*(current month's sales), where: BaseLevList - D26:F26
13 A B C Loans - D44:I44
14 0.2 0.6 0.2 IntPayments - E41:J41
15
16 Cost of materials and labor for next month, spent this month, is a percentage of product's sales from next month, where the percentage is:
17 80%
18
19 Initial cash in Jan 250
20 Min cash balance 250
21
22 Monthly interest rates
23 Loan interest rate 1.0%
24 Interest rate on cash 0.5%
25 Base levels to investigate
26 Base level of sales #NAME? 80% 100% 120%
27
28 Simulation section
29 Nov Dec Jan Feb Mar Apr May Jun Jul
30 Actual sales #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
31
32 Cash, receipts
33 Beginning cash balance 250.000 #NAME? #NAME? #NAME? #NAME? #NAME?
34 Interest on cash balance 1.250 #NAME? #NAME? #NAME? #NAME? #NAME?
35 Receipts #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
36 Costs
37 Fixed costs 250 250 250 250 250 250
38 Tax, dividend expenses 0 0 150 0 0 100
39 Material, labor expenses #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
40 Loan payback (principal) #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
41 Loan payback (interest) #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
42
43 Cash balance before loan #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
44 Loan amount (if any) #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
45 Final cash balance #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
46
47 Maximum loan #NAME?
48 Total intest on loans #NAME?