Question

In: Finance

Using a spreadsheet, prepare a cash budget for New Tech for the next year (January through...

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 (December)

PAYMENT SCHEDULE

Cash sales 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 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.0 45.0 33.0 350.0 500 16.8 23.8 23.0 18.0 20.0 20.0 10.0

Solutions

Expert Solution

Summarising Sales and purchase breakdown;

SALES
Cash sales	10%
30-day payment	70%
60-day payment	20%
Purchases
30-day payment	40%
60-day payment	60%

Calculation of Previous Year's Cash Flows

	OCT	NOV	DEC
Sales	325.00	325.00	325.00
Purchases			59.20
Collection From Sales
Cash sales 10% Current Month	32.50	32.50	32.50
30-day payment (70%* previous month)		227.50	227.50
60-day payment (20%* 2 month before)			65.00
Cash Flows for the month	32.50	260.00	325.00
Balance from previous month		32.50	292.50
Balance C/f to Next month	32.50	292.50	617.50

Current Year's Cash Budget

Particulars	JAN	FEB	MAR	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC
Sales	325.00	300.00	275.00	325.00	350.00	375.00	400.00	375.00	350.00	300.00	275.00	250.00
Purchases	59.20	59.20	59.20	59.20	59.20	59.20	59.20	59.20	59.20	59.20	59.20	59.20
Collection From Sales
Cash sales 10% Current Month	32.50	30.00	27.50	32.50	35.00	37.50	40.00	37.50	35.00	30.00	27.50	25.00
30-day payment (70%* previous month)	227.50	227.50	210.00	192.50	227.50	245.00	262.50	280.00	262.50	245.00	210.00	192.50
60-day payment (20%* 2 month before)	65.00	65.00	65.00	60.00	55.00	65.00	70.00	75.00	80.00	75.00	70.00	60.00
Total Cash Inflow from Sales	325.00	322.50	302.50	285.00	317.50	347.50	372.50	392.50	377.50	350.00	307.50	277.50
Payments for Purchases
30 Day (40%* Previous Month)	(23.68)	(23.68)	(23.68)	(23.68)	(23.68)	(23.68)	(23.68)	(23.68)	(23.68)	(23.68)	(23.68)	(23.68)
60 Day (60% *two months before)	0.00	(35.52)	(35.52)	(35.52)	(35.52)	(35.52)	(35.52)	(35.52)	(35.52)	(35.52)	(35.52)	(35.52)
Total Cash Outflow for Purchases	(23.68)	(59.20)	(59.20)	(59.20)	(59.20)	(59.20)	(59.20)	(59.20)	(59.20)	(59.20)	(59.20)	(59.20)

Disbursements
Freight	(4.20)	(4.00)	(5.10)	(5.40)	(7.30)	(6.50)	(6.70)	(6.60)	(6.10)	(7.00)	(5.80)	(5.70)
Labor	(40.00)	(39.00)	(38.00)	(42.00)	(38.40)	(44.30)	(42.50)	(41.00)	(38.00)	(39.00)	(39.50)	(38.00)
Utilities Insurance, etc.	(2.50)	(2.20)	(2.40)	(2.50)	(2.50)	(2.80)	(2.70)	(2.50)	(2.60)	(2.60)	(2.60)	(2.40)
Salaries- Selling	(60.00)	(62.00)	(65.80)	(62.80)	(62.70)	(67.90)	(71.60)	(62.80)	(64.90)	(66.90)	(68.00)	(65.00)
Commissions	(5.90)	(5.00)	(5.80)	(6.80)	(6.90)	(7.40)	(8.00)	(7.80)	(6.50)	(6.80)	(6.40)	(6.40)
Travel	(7.00)	(7.10)	(6.90)	(6.80)	(7.60)	(7.80)	(8.40)	(7.50)	(7.70)	(7.70)	(7.90)	(7.40)
Advertising	(6.00)	(6.50)	(7.00)	(9.00)	(8.50)	(8.70)	(8.30)	(8.10)	(7.00)	(7.20)	(6.00)	(7.50)
Other charges	(5.00)	(6.00)	(3.00)	(40.00)	(50.00)	(21.50)	(18.30)	(18.70)	(18.90)	(16.00)	(8.50)	(14.00)
Selling Salaries	(35.00)	(35.00)	(34.00)	(33.00)	(36.00)	(36.00)	(38.00)	(39.00)	(35.00)	(33.00)	(32.00)	(34.00)
Leasing	(5.80)	(5.80)	(5.80)	(5.80)	(5.80)	(5.80)	(5.80)	(5.80)	(5.80)	(5.80)	(5.80)	(5.80)
Research	(8.30)	(10.30)	(15.60)	(7.50)	(8.30)	(7.70)	(7.90)	(6.20)	(6.70)	(6.90)	(6.30)	(8.30)
Taxes	(9.30)	(9.30)	(9.30)	(9.30)	(9.30)	(9.30)	(9.30)	(9.30)	(9.30)	(9.30)	(9.30)	(9.30)
Cash Outflow For Disbursements	(189.00)	(192.20)	(198.70)	(230.90)	(243.30)	(225.70)	(227.50)	(215.30)	(208.50)	(208.20)	(198.10)	(203.80)
Total Cash Outflow from Operating Activities	(212.68)	(251.40)	(257.90)	(290.10)	(302.50)	(284.90)	(286.70)	(274.50)	(267.70)	(267.40)	(257.30)	(263.00)
Cash Flow From Operating Activities	112.32	71.10	44.60	(5.10)	15.00	62.60	85.80	118.00	109.80	82.60	50.20	14.50
Cash Out Flow From Investing Activities
Purchase of assets	(40.00)	(45.00)	(33.00)	(350.00)	(500.00)	(16.80)	(23.80)	(23.00)	(18.00)	(20.00)	(20.00)	(10.00)

Cash Flow For the Month	72.32	26.10	11.60	(355.10)	(485.00)	45.80	62.00	95.00	91.80	62.60	30.20	4.50
Balance b/f from Previous month	617.50	689.82	715.92	727.52	372.42	(112.58)	(66.78)	(4.78)	90.22	182.02	244.62	274.82

Balance at the end of month	689.82	715.92	727.52	372.42	(112.58)	(66.78)	(4.78)	90.22	182.02	244.62	274.82	279.32

* Note: Assumed Negative Cash Balance Implies a Bank Overdraft
Note 2: The reason for negative Cash Balance in May, June and July: Purchase of Assets 350 and 500 in month of April and May,respectively

jeff jeffy answered 1 month ago

Next > < Previous

Related Solutions

Using a spreadsheet, prepare a cash budget for New Tech for the next year (January through...

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...

. Prepare a cash budget for January through March and for the first quarter in...

. Prepare a cash budget for January through March and for the first quarter in total. The company maintains a minimum cash balance of $70,000, and this was the balance in the cash account on January 1st. Other expenses include $35,000 per month for rent, $24,000 per month for advertising, and $66,000 per month for depreciation. In addition, variable Selling & Administrative cost is $12 per unit sold, and the company paid a $20,000 dividend in February. The company...

Prepare a monthly cash budget for January through April. Be sure to clearly show the surplus...

Prepare a monthly cash budget for January through April. Be sure to clearly show the surplus (deficit) of cash. Based on the deficit cash shown on your cash budget, what is the dollar amount that Bond Private Security should have for a line of credit over this 4-month period? Note: don’t add a “cushion”, just base this amount off of the cash budget. Collections during month of sale 35% Collections during month after sale 50% Collections during second month after...

I. (25) Cash Budget for New Toys Company: Prepare a cash budget and provide advices for...

I. (25) Cash Budget for New Toys Company: Prepare a cash budget and provide advices for the second quarter of 201x based upon the following information:  The firms’ sales during February and March of 201x were $1,528,000 and $1,662,000, respectively. Sales during the next four months are projected to be : April $1,950,000 May $2,700,000 June $2,400,000 July $2,100,000  Historically, 100% of the firm’s sales are on credit, with about 60% of each month’s sales collected one month...

Prepare a cash budget (using info from the Sales Budget and purchases budget shown below) for...

Prepare a cash budget (using info from the Sales Budget and purchases budget shown below) for January through March and for the first quarter in total. The company maintains a minimum cash balance of $50,000.00, and this was the balance in the cash account on January 1. Past experience shows that 30% of sales are collected in the month of the sale, and 70% in the month following the sale. Labor cost is $15 per unit. Other expenses include $37,000...

Lamia's House of Software (LHS) wants to prepare a cash budget for months of September through...

Lamia's House of Software (LHS) wants to prepare a cash budget for months of September through Dêcember. Using the following information, Sales were $50,000 in June and $60,000 in July. Sales have been forecasted to be $65,000, $72,000, $63,000, $59,000, and $56,000 for months of August, September, October, November, and December, respectively. In the past, 10 percent of sales were on cash basis, and the collection were 50 percent in the first month, 30 percent in the second month, and...

Lamia's House of Software (LHS) wants to prepare a cash budget for months of September through...

Lamia's House of Software (LHS) wants to prepare a cash budget for months of September through December. Using the following information, ∙ Sales were $50,000 in June and $60,000 in July. Sales have been forecasted to be $65,000, $72,000, $63,000, $59,000, and $56,000 for months of August, September, October, November, and December, respectively. In the past, 10 percent of sales were on cash basis, and the collection were 50 percent in the first month, 30 percent in the second month,...

The finance manager wants to prepare a cash budget for the July, 2020 through December, 2020...

The finance manager wants to prepare a cash budget for the July, 2020 through December, 2020 period. The finance manager has received the following information from the marketing and operations managers: • The Sales were $140,000 in January, 2020 and then the sales grew by 2% each month in the first three months (i.e., from February to April 2020) and by 5% in the next two months (i.e., in May and June 2020). The sales are expected to grow by...

1. Prepare a sales budget for January through May. The selling price per unit is $40.00....

1. Prepare a sales budget for January through May. The selling price per unit is $40.00. December of the previous year-40,000 January-90,000 February-80,000 March-70,000 April-40,000 2. Prepare a purchases budget for January through March, and the first quarter in total. Assume that the company only sells one product that can be purchased at $15.00 per unit. The market for this product is very competitive and customers highly value service such as quality and on time delivery of the product. Also...

Subjects