Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
￿
Calculations
￿
Income and Cash Flow Statements
￿
Internal Rate of Return Calculation
Note that the Internal Rate of Return Calculation section is new to the spreadsheet model for this edition
of Problem Solving Cases. The section was added because Excel financial formulas such as IRR work better if
the cash outflow and inflow data is arranged in a vertical column with the years in ascending order, as
opposed to taking the cash flows from across the page or from nonadjacent cells.
Constants Section
First, build the skeleton of your spreadsheet. Set up your Constants section, as shown in Figure 6-1. An
explanation of the line items follows the figure.
FIGURE 6-1
Constants section
￿
Direct Labor Cost/Hr, Future Cars Inc.
This value is the cost per direct labor hour worked on
the manufacturing line, to include payroll taxes and benefits.
￿
Direct Materials Cost per Electric Hybrid Car
This value is the cost of the parts needed to build
one electric hybrid car, with a three percent inflation factor built in for each year.
￿
Direct Materials Cost per Fuel Cell Car
This value is the cost of the parts needed to build one
fuel-cell car, with a three percent inflation factor built in for each year.
￿
Direct Labor Hours per Electric Hybrid Car
This value is the number of direct labor hours
needed to build one electric hybrid car, with a five percent learning factor built in for each year.
(The learning factor reflects the increasing efficiency of labor with experience.)
￿
Direct Labor Hours per Fuel Cell Car
This value is the number of direct labor hours needed to
build one fuel-cell car, with a five percent learning factor built in for each year of production.
￿
Overhead Allocation Rate per Direct Labor Hour (both technologies)
This value is the
estimated amount of manufacturing overhead costs, such as indirect materials, indirect labor, and
depreciation of plant, maintenance, and repair materials. These costs are allocated to the cost of
the car based on the number of direct labor hours needed to make the car. Overhead can be
allocated in a number of ways, but Future Cars Inc. allocates on the basis of direct labor because
the assembly process is labor-intensive.
￿
Capital Investment for Electric Hybrid Car
This value is the total amount of investment in
retooled plant equipment and technology to produce the electric hybrid car.
￿
Capital Investment for Fuel Cell Car
This value is the total amount of investment in retooled
plant equipment and technology to produce the fuel-cell car.
￿
Median Alternative Energy Car Sales Price, 2012
This value is the median selling price for
current alternative-energy cars in 2012. This value is important for determining a competitive price
for new Future Cars models.
￿
Corporate Income Tax Rate
These values are the predicted corporate income tax rates for
Future Cars for the new technology. Note that these rates are lower than usual corporate income
tax rates because Future Cars will receive R&D tax credits for its new technology.

Search JabSto ::

Custom Search