Capital budgeting/cash budget/EFN

Complete each tab, based on the spreadsheet problems.

Chapter 1 Spreadsheet Exercise
Assume that Monsanto Corporation is considering the renovation and/or replacement of some of its older and outdated carpet-manufacturing equipment. Its objective is to improve the
efficiency of operations in terms of both speed and reduction in the number of defects. The company's finance department has compiled pertinent data that will allow it to conduct a marginal
cost-benefit analysis for the proposed equipment replacement.

The cash outlay for new equipment would be approximately $600,000. The net book value of the old equipment and its potential net selling price is $250,000.
The total benefits from the new equipment (measured in today's dollars) would be $900,000. The benefits of the old equipment over a similar period of time (measured in today's dollars) would be $300,000.
Instructions
Create a spreadsheet to conduct a marginal cost-benefit analysis for Monsanto Corporation and determine the following:

1. The marginal (added) benefits of the proposed new equipment.
2. The marginal (added) cost of the proposed new equipment.
3. The net benefit of the proposed new equipment.
4. What would you recommend that the firm do? Why?

Chapter 3 Spreadsheet Exercise
You have been assigned the task of putting together a statement for the ACME Company that shows its expected inflows and outflows of cash over the months of July 2010 through December 2010.
You have been given the following data for ACME Company:

1. Expected gross sales for May through December, respectively, are $300,000, $290,000, $425,000, $500,000, $600,000, $625,000, $650,000, and $700,000.
2. 12% of the sales in any given month are collected during that month. However, the firm has a credit policy of 3/10 net 30, so factor a 3% discount into the current month's sales collection.
3. 75% of the sales in any given month are collected during the following month after the sale.
4. 75% of the sales in any given month are collected during the following month after the sale.
4. 13% of the sales in any given month are collected during the second month following the sale.
5. The expected purchases of raw materials in any given month are based on 60% of the expected sales during the following month.
6. The firm pays 100% of its current month's raw materials purchases in the following month.
7. Wages and salaries are paid on a monthly basis and are based on 6% of the current month's expected sales.
8. Monthly lease payments are 2% of the current month's expected sales.
9. The monthly advertising expense amounts to 3% of sales.
10. R&D expenditures are expected to be allocated to August, September, and October at the rate of 12% of sales in those months.
11. During December a prepayment of insurance for the following year will be made in the amount of $24,000.
12.During the months of July through December, the firm expects to have miscellaneous expenditures of $15,000, $20,000, $25,000, $30,000, $35,000, and $40,000, respectively.
13. Taxes will be paid in September in the amount of $40,000 and in December in the amount of $45,000.
14. The beginning cash balance in July is $15,000.
15. The target cash balance is $15,000.
16. The firm can invest its surplus cash to earn a 6% annual return.

Instructions
Prepare a cash budget for July 2010 through December 2010 by creating a combined spreadsheet that incorporates spreadsheets similar to those in Tables 3.8, 3.9, and 3.10. Divide your spreadsheet into three sections:

1. Collections from sales and payments to purchase inventory
2. Operating expenditures over the time period
3. Cash budget covering the period of July through December
The cash budget should reflect the following:
1. Beginning and ending monthly cash balances
2. The month(s) in which there will be a cash deficit
3. The month(s) which there will be a cash surplus
4. The cumulative cash deficit and/or cash surplus

Based on your analysis, briefly describe the outlook for this company over the next 6 months. Discuss its specific obligations and the funds available to meet them.
What could the firm do in the case of a cash deficit? (Where could it get the money?) What should the firm do if it has a cash surplus?

Chapter 3 EFN- Essay questions
( Type your response in the EFN tab in this file)
A- What is EFN? Is it a calculation or a plug? Why is EFN important in strategic planning?
B-When figuring EFN what statements are critical to use? Is cash flow important in figuring EFN?

Chapter 14 Spreadsheet exercise
The current balance in accounts receivable for Eboy Corporation is $443,000. This level was achieved with annual (365 days) credit sales of $3,544,000.
The firm offers its customers credit terms of net 30. However, in an effort to help its cash flow position and to follow the actions of its
rivals, the firm is considering changing its credit terms from net 30 to 2/10 net 30. The objective is to speed up the receivable collections and thereby improve the firm's cash flows.
Eboy would like to increase its accounts receivable turnover to 12.0.

The firm works with a raw material whose current annual usage is 1,450 units. Each finished product requires 1 unit of this raw material at a variable cost of $2,600 per unit and sells for
$4,200 on terms of net 30. It is estimated that 70% of the firm's customers will take the 2% cash discount and that with the discount, sales of the finished product will increase by 50 units per
year. The firm's opportunity cost of funds invested in accounts receivable is 12.5%.

In analyzing the investment in accounts receivable, use the variable cost of the product sold instead of the sale price, because the variable cost is a better indicator of the firm's investment.
Instructions
Create a spreadsheet similar to Table 14.3 to analyze whether the firm should initiate the proposed cash discount. What is your advice? Make sure you calculate the following:
a. Additional profit contribution from sales.
b. Average investment in accounts receivable at present (without cash discount).
c. Average investment in accounts receivable with the proposed cash discount.
d. Reduction in investment in accounts receivable.
e. Cost savings from reduced investment in accounts receivable.
f. Cost of the cash discount.
g. Net profit (loss) from initiation of proposed cash discount.