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 7 months agoRelated 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...
Required: Prepare a complete statement of cash flows using a spreadsheet; report its operating activities using...
Required:
Prepare a complete statement of cash flows using a spreadsheet;
report its operating activities using the indirect method.
(Enter all amounts as positive values.)
Please use the indirect method
Required information
Use the following information for the Problems below.
[The following information applies to the questions displayed
below.]
Forten Company, a merchandiser, recently completed its
calendar-year 2017 operations. For the year, (1) all sales are
credit sales, (2) all credits to Accounts Receivable reflect cash
receipts from customers, (3)...
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 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...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
- Case Study: Personal Beliefs in the Workplace Don Shakow was a prominent professional who worked in...
- 1: Competency framework usage is the modern best practice in management. What is meant by competency...
- A 19.34 g mixture of sugar (C12H22O11) and table salt (NaCl) is dissolved in 231 g...
- Young and Company claims that its pressurized diving bell will, on average, maintain its integrity to...
- 1) Alternative compiled code sequence using instructions in classes A, B, C. What is the average...
- An asset is purchased on January 1 for $48,700. It is expected to have a useful...
- Do we have any online or offline tool to convert c and cpp programs into MIPS...
ADVERTISEMENT