Question

Using a spreadsheet, prepare a cash budget for New Tech for the next year (January through December) based on the following information.

OCT NOV DEC

Sales revenue (previous year) 325.0 325.0 325.0

Purchases 59.2

PAYMENT SCHEDULE

Cash sales 5 10%
30-day payment = 70%

60-day payment = 20%

PURCHASE SCHEDULE

Paid in first month = 40%

Paid during second 60 days = 60%

Jan    Feb   Mar   Apr   May   Jun    Jul   Aug   Sep   Oct Nov   Dec

Sales Revenue                  325   300   275    325   350   375    400   375   350   300   275   250

Purchases                           59.0 59.2 59.2   59.2 59.2 59.2 59.2 59.2 59.2 59.2 59.2 59.2

Disbursements

Freight in                             4.2   4.0     5.1     5.4    7.3    6.5      6.7    6.6    6.1   7.0   5.8    5.7

Labor                                   40.0 39.0 38.0 42.0 38.4 44.3   42.5 41.0 38.0 39.0 39.5 38.0

Utilities,                               2.5    2.2     2.4     2.5     2.5     2.8      2.7     2.5    2.6   2.6    2.6    2.4

Insurance, etc.

Salaries – selling               60.0 62.0   65.8   62.8 62.7   67.9    71.6   62.8   64.9 66.9 68.0 65.0

Commissions                     5.9    5.0      5.8     6.8    6.9     7.4      8.0      7.8     6.5     6.8     6.4    6.4

Travel 7.0    7.1      6.9     6.8   7.6      7.8      8.4      7.5     7.7     7.7     7.9    7.4

Advertising                         6.0    6.5      7.0     9.0   8.5      8.7      8.3       8.1    7.0     7.2     6.0     7.5

Other charges                    5.0    6.0      3.0    40.0 50.0    21.5    18.3     18.7 18.9   16.0   8.5    14.0

Selling

Salaries                             35.0 35.0   34.0   33.0 36.0   36.0    38.0     39.0   35.0   33.0   32.0   34.0

administration

Leasing                             5.8   5.8     5.8       5.8    5.8     5.8     5.8     5.8         5.8     5.8     5.8     5.8

Research n                       8.3   10.3   15.6    7.5   8.3     7.7     7.9     6.2        6.7     6.9     6.3     8..3

development

Taxes                                9.3   9.3       9.3    9.3     9.3      9.3     9.3    9.3       9.3      9.3      9.3     9.3

Purchase of assets          40.0 45.0 33.0   350.0 500   16.8   23.8   23.0   18.0    20.0    20.0   10.0

Answer 1

	oct	nov	dec	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
sales rev	325	325	325	325	300	275	325	350	375	400	375	350	300	275	250
opening cash	0	8.82	209.62	475.42	536.3	565.52	569.92	405.02	10.02	-234.1	-167.9	-73.38	15.42	79.22	109.42	107.92
cash sales
10%	32.5	32.5	32.5	32.5	30	27.5	32.5	35	37.5	40	37.5	35	30	27.5	25
70%		227.5	227.5	227.5	227.5	210	192.5	227.5	245	262.5	280	262.5	245	210	192.5
20%			65	65	65	65	60	55	65	70	75	80	75	70	60
total cash	32.5	268.82	534.62	800.42	858.8	868.02	854.92	722.52	357.52	138.4	224.6	304.12	365.42	386.72	386.92
freight				4.2	4	5.1	5.4	7.3	6.5	6.7	6.6	6.1	7	5.8	5.7
labor				40	39	38	42	38.4	44.3	42.5	41	38	39	39.5	38
utilities				2.5	2.2	2.4	2.5	2.5	2.8	2.7	2.5	2.6	2.6	2.6	2.4
salaries				60	62	65.8	62.8	62.7	67.9	71.6	62.8	64.9	66.9	68	65
comm				5.9	5	5.8	6.8	6.9	7.4	8	7.8	6.5	6.8	6.4	6.4
travel				7	7.1	6.9	6.8	7.6	7.8	8.4	7.5	7.7	7.7	7.9	7.4
advertising				6	6.5	7	9	8.5	8.7	8.3	8.1	7	7.2	6	7.5
other charges				5	6	3	40	50	21.5	18.3	18.7	18.9	16	8.5	14
salaries admin				35	35	34	33	36	36	38	39	35	33	32	34
leasing				5.8	5.8	5.8	5.8	5.8	5.8	5.8	5.8	5.8	5.8	5.8	5.8
rnd				8.3	10.3	15.6	7.5	8.3	7.7	7.9	6.2	6.7	6.9	6.3	8.3
taxes				9.3	9.3	9.3	9.3	9.3	9.3	9.3	9.3	9.3	9.3	9.3	9.3
purchase of assets				40	45	33	350	500	16.8	23.8	23	18	20	20	10
purchases	59.2	59.2	59.2	59	59.2	59.2	59.2	59.2	59.2	59.2	59.2	59.2	59.2	59.2	59.2
Total purchases	59.2	59.2	59.2	99	104.2	92.2	409.2	559.2	76	83	82.2	77.2	79.2	79.2	69.2
purchase payment Schedule
40%	23.68	23.68	23.68	39.6	41.68	36.88	163.68	223.68	30.4	33.2	32.88	30.88	31.68	31.68	27.68
60%		35.52	35.52	35.52	59.4	62.52	55.32	245.52	335.52	45.6	49.8	49.32	46.32	47.52	47.52
Total outflow	23.68	59.2	59.2	264.12	293.28	298.1	449.9	712.5	591.62	306.3	297.98	288.7	286.2	277.3	279
excess/deficit cash	8.82	209.62	475.42	536.3	565.52	569.92	405.02	10.02	-234.1	-167.9	-73.38	15.42	79.22	109.42	107.92

Few Assumptions:

1) No minimum cash balance required, as nothing is mentioned in the question

2) one-time payment for all expenses is done, as the payment schedule is considered only for the purchase of raw material and assets.