Log in
Finance
1
answer
0
watching
154
views

Table 3.3 shows the December 31, 2015 pro forma balance sheet and income statements for R&E Supplies Inc. The pro-forma balance sheet shows that R&E Supplies will need external funding from the bank of $1.4 million. However, they show almost $1.3 million in cash and short term securities. What are they talking to the bank for such a large amount when they have most of this sum in their cash account?

Watch
1
answer
0
watching
154
views

For unlimited access to Homework Help, a Homework+ subscription is required.

Lelia Lubowitz
Lelia LubowitzLv2
29 Sep 2019

Unlock all answers

Get 1 free homework help answer.
Already have an account? Log in
Share

Related questions

TABLE 3.3 Pro Forma Financial Statements for R&E Supplies,Inc., December 31, 2018 ($ thousands)

Income Statement

2018

Comments

Net sales

$25,766

25% increase

Cost of goods sold

22,159

86% of sales

Gross profit

3,607

Expenses:

 General, selling, and administrative expenses

3,092

12% of sales

 Net interest expense

90

Initially constant

Earnings before tax

425

Tax

191

45% tax rate

Earnings after tax

$ 234

Balance Sheet

Assets

Current assets:

 Cash and securities

$ 1,271

18 days sales

 Accounts receivable

3,600

51-day collection period

 Inventories

2,462

9 times turnover

 Prepaid expenses

20

Rough estimate

  Total current assets

7,353

Net fixed assets

280

See text discussion

Total assets

$ 7,633

Liabilities and Owners’ Equity

Current liabilities:

 Bank loan

$ 0

 Accounts payable

3,582

59-day payables period

 Current portion of long-term debt

100

See text discussion

 Accrued wages

22

Rough estimate

  Total current liabilities

3,704

Long-term debt

660

Common stock

150

Retained earnings

1,697

See text discussion

Total liabilities and owners’ equity

$ 6,211

External funding required

$ 1,422

Table 3.3 shows the December 31, 2018 pro forma balance sheetand income statements for R&E Supplies, Inc. The pro-formabalance sheet shows that R&E Supplies will need externalfunding from the bank of $1.4 million. However, they show almost$1.3 million in cash and short-term securities. Why are theytalking to the bank for such a large amount when they have most ofthis sum in their cash account?

(If possible can you please complete in an excel document)

Excel Project Instructions

Assume ABC Company has asked you to not only prepare their 2015 year-end Balance Sheet but to also provide pro-forma financial statements for 2016. In addition, they have asked you to evaluate their company based on the pro-forma statements with regard to ratios. They also want you to evaluate 3 projects they are considering. Their information is as follows:

End of the year information:

Account

12/31/15

Ending Balance

Cash

50,000

Accounts Receivable

175,000

Inventory

126,00

Equipment

480,000

Accumulated Depreciation

90,000

Accounts Payable

156,000

Short-term Notes Payable

12,000

Long-term Notes Payable

200,000

Common Stock

235,000

Retained Earnings

solve

Additional Information:

Sales for December total 10,000 units. Each month’s sales are expected to exceed the prior month’s results by 5%. The product’s selling price is $25 per unit.

Company policy calls for a given month’s ending inventory to equal 80% of the next month’s expected unit sales. The December 31 2015 inventory is 8,400 units, which complies with the policy. The purchase price is $15 per unit.

Sales representatives’ commissions are 12.5% of sales and are paid in the month of the sales. The sales manager’s monthly salary will be $3,500 in January and $4,000 per month thereafter.

Monthly general and administrative expenses include $8,000 administrative salaries, $5,000 depreciation, and 0.9% monthly interest on the long-term note payable.

The company expects 30% of sales to be for cash and the remaining 70% on credit. Receivables are collected in full in the month following the sale (none is collected in the month of sale).

All merchandise purchases are on credit, and no payables arise from any other transactions. One month’s purchases are fully paid in the next month.

The minimum ending cash balance for all months is $50,000. If necessary, the company borrows enough cash using a short-term note to reach the minimum. Short-term notes require an interest payment of 1% at each month-end (before any repayment). If the ending cash balance exceeds the minimum, the excess will be applied to repaying the short-term notes payable balance.

Dividends of $100,000 are to be declared and paid in February.

No cash payments for income taxes are to be made during the first calendar quarter. Income taxes will be assessed at 35% in the quarter.

Equipment purchases of $55,000 are scheduled for March.

ABC Company’s management is also considering 3 new projects consisting of the purchase of new equipment. The company has limited resources, and may not be able to complete make all 3 purchases. The information is as follows for the purchases below.

Project 1

Project 2

Project 3

Purchase Price

$80,000

$175,000

$22,700

Required Rate of Return

6%

8%

12%

Time Period

3 years

5 years

2 years

Cash Flows – Year 1

$48,000

$85,000

$15,000

Cash Flows – Year 2

$36,000

$74,000

$12,000

Cash Flows – Year 3

$22,000

$38,000

N/A

Cash Flows – Year 4

N/A

$26,800

N/A

Cash Flows – Year 5

N/A

$19,000

N/A

Required Action:

Part A:

Prepare the year-end balance sheet for 2015. Be sure to use proper headings.

Prepare budgets such that the pro-forma financial statements for the first quarter of 2016 may be prepared.

Sales budget, including budgeted sales for April.

Purchases budget, the budgeted cost of goods sold for each month and quarter, and the cost of the March 31 budgeted inventory.

Selling expense budget.

General and administrative expense budget.

Expected cash receipts from customers and the expected March 31 balance of accounts receivable.

Expected cash payments for purchases and the expected March 31 balance of accounts payable.

Cash budget.

Budgeted income statement.

Budgeted statement of retained earnings.

Budgeted balance sheet.

Part B:

Calculate using Excel formulas, the NPV of each of the 3 projects.

It is possible that ABC Company may not be able to complete all 3 projects. Therefore, advise ABC Company as to the order in which they should pursue the projects (i.e., which project should ABC Company attempt to do first, second, and last).

Provide justification and analysis as to why you chose the order you did. The analysis must also be done in Excel, not in a separate document.

Years are 2014 and 2015.

ABC is in its 4th year of operations. The company is experiencing cash flow issues and would like to utilize a budget to forecast cash flows. You are an outside consultant hired to assist in designing a model that ABC can utilize on an ongoing basis to forecast cash flow. The president of the company, Ms. Dream, has predicted an increase in sales of two percent per month from December 2014 through March 2015 Mr. Dream believes that after March, sales will remain steady through June 2015. The projected balance sheet for ABC as of December 31, 2015 is shown below: Cash $ 20,000 A/receivable 208,748 Marketable securities 25,000 Inventory 25,750 Buildings & equipment (net of accumulated depreciation) 225,000 Total assets $ 504,498 A/payable $ 101,384 Sales commissions payable 6,885 Bond interest payable 4,800 Property taxes payable 0 Bonds payable (4%- due in 2020) 360,000 Common stock 10,000 Retained earnings 21,429 Total liabilities and stockholders' equity $ 504,498 You will need the following information to prepare a master budget for the first quarter of 2015: 1) Projected sales for November 2015 are $225,000. Credit sales comprise 95% of total sales. Sales collected in the month of sale total 15%, in the month following the month of sale total 70%, and in the second month following the month of sale total 13%. Remaining sales are uncollectible. 2) Cost of goods sold total 55% of sales. Inventory purchases are made on account. Purchases paid for in the month of purchase total 20% and purchases paid for in the month following the month of purchase total 80%. Ending monthly Inventory is maintained at 20% of the following month’s projected cost of goods. 3) Projected monthly expenses include: Sales salaries $ 60,000 Advertising and promotion 4,000 Administrative salaries 25,000 Depreciation 3,000 Interest on bonds 1,200 Property taxes 1,500 Sales commissions of 3.0 percent of sales are paid in the month following the month of sale. 4) Additional manufacturing equipment would allow the company to increase production. The cost of the new equipment is $150,000. Ms. Dream plans on purchasing the equipment on January 1st and plans on paying for the equipment with cash and by liquidating the company’s marketable securities. Mr. Clark wishes to maintain a minimum cash balance of $15,000 at the end of each month. If necessary, he plans on obtaining a short-term note payable from a local bank. The minimum lending period for such a loan is three months. The current short-term interest rate is 7 percent per year and is expected to remain at this rate through the time the equipment is purchased. Mr. Clark would like to pay off the loan as quickly as possible. Payments must be made in $1,000 increments. 5) The board of directors would like to declare and pay dividends of $125,000 on the last day of each quarter. 6) The interest on any short-term borrowing will be paid when the loan is repaid. Interest on the bond payable is paid semiannually on February 28 and August 31 for the preceding six-month period. 7) Property taxes are paid quarterly on March 31, June 30, September 30, and December 31 for the preceding three-month period. Required: Assist ABC by building a model to forecast cash flow. Set up your model for the first quarter of 2015. The model should contain the master budget schedules shown below. Round all amounts to the nearest dollar. The model should allow ABC to change the assumptions provided above and quickly recalculate the forecasted cash balance at March 31, 2015. The assumptions may be included on a separate worksheet in your Excel file but all of the schedules below must be on one worksheet. Please Prepare Summary Cash budget in following table with all shown calculations and formulas.

Summary Cash Budget
January February March 1st Quarter
Cash receipts(Sch 2)
Less: Cash disbursement (Sch 4)
Change in cash balance during period due to operations
Sales of marketable securities(1/1/15)
Proceeds from bank loan
Purchase of equipment
Repayment of bank loan(03/31/15)
Interest on bank loan
Payment of dividends
Change in cash balance during the month
Beginning cash balance
Ending cash balance

Weekly leaderboard

Home
Homework Help 3,900,000
Finance 370,000
Start filling in the gaps now
Log in