Explore BrainMass
Share

# Application Software Exercise: Spreadsheet Exercise

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

Please convert this CAD.doc file into excel with all the formulas. I need this today within three hours (need to see the excel formula involved). By the way, Kevin forgot to answer this question: Is this investment worthwhile? Why or why not?

Just want double check my calculation - all work must be performed in Excel using Excel built-in formulas. Thanks. (extra credit)

See attached.

Problem: Your Company would like to invest in a new computer-aided-design (CAD) system that requires purchasing hardware, software, and networking technology, as well as expenditures for installation, training, and support. The tables showing each cost component for the new system as well as annual maintenance costs over a five-year period are listed below. You believe the new system will produce annual savings by reducing the amount of labor required to generate designs and design specifications, thus increasing cash flow. Using the data provided and instructions at the Web site for this chapter create a worksheet that calculates the costs and benefits of the investment over a five-year period and analyzes the investment using the six capital budgeting models presented in this Chapter 15. Is this investment worthwhile? Why or why not?

? Payback period - indicate yearly and cumulative figures for years 2005-2009
? Accounting Rate of Return on Investment (ROI). Assume depreciation to be the total initial cost of the investment in Year 0.
? Cost-Benefit Ratio
? Net Present Value, assuming an interest rate of 5%
? Profitability Index
? Internal Rate of Return

See attached.

Thanks, Gary

\$2.19

## A spreadsheet to support the decision making needs of SCTC's managers

SPREADSHEET EXERCISE: PERFORMING BREAKEVEN ANALYSIS AND SENSITIVITY ANALYSIS

Selmore Collectible Toy Company (SCTC) makes toy sets consisting of collectible trucks, vans, and cars for the retail market. The firm is developing a new toy set that includes a battery-powered tractor-trailer, complete with cab and trailer, sports car, and motorcycle. Each set sells for \$100. Table 1 shows the major components of SCTC's annual fixed costs for the toy set. Each component includes the cost of purchases, depreciation, and operating expenses. Table 2 shows the major components of SCTC's variable costs.

Prepare a spreadsheet to support the decision making needs of SCTC's managers. The spreadsheet should show the fixed costs, variable costs per unit, the contribution margin, and the breakeven point for this product. How many sets does SCTC have to sell before it can start turning a profit? Include a data table to show alternative breakeven points, assuming variations in insurance costs and labour costs. How would increasing the sale price to \$125 affect the breakeven point?
Table 1 SCTC Fixed Costs
Category Amount
Land \$ 42 500
Buildings 332 500
Manufacturing machinery 532 000
Office equipment 212 800
Utilities 30 500
Insurance \$1 250 000
Total \$1 250 000
Table 2 SCTC Variable Costs
Category Amount
Labour \$15.00