Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
￿
New Product Capital Investment
This value is the amount of investment money spent at the
end of 2012, depending on the Technology Selection from the Inputs section. If the selected
technology is Electric Hybrid (E), this cell should display the capital investment amount from
the Constants section for the electric hybrid (the value in cell B10). If the selected technology
is Fuel Cell (F), the cell should display the capital investment amount from the Constants
section for the fuel-cell car (the value in cell B11). Note that when you see the word
if
in
the text, you need to write a formula using the IF function for the target cell.
￿
Total Forecasted U.S. Market Demand for Alternative Energy Cars
You are given the forecasted
U.S. market demand for alternative-energy cars for 2012. You will have to write formulas to
calculate the forecasted U.S. market demand for 2013, 2014, and 2015. The Marketing
Department thinks that the U.S. market demand for alternative-energy cars will depend on both
the Economic Outlook and the Gasoline Price Outlook for the next three-year cycle. These
outlooks have four possible combinations and results:
￿
Recession and low gas prices
Market demand for each year will only be 95 percent of the
previous year
s demand.
￿
Recession and high gas prices
Market demand for each year will only be 98 percent of the
previous year
s demand.
￿
Boom and low gas prices
Market demand for each year will be exactly the same as the
previous year
s demand (or 100 percent of the previous year).
￿
Boom and high gas prices
Market demand for each year will be two percent higher than the
previous year
s demand (or 102 percent of the previous year).
(Hint: For these forecasts, you will have to use a nested IF statement that contains AND
functions.)
￿
Forecasted Sales for Future Cars
market penetration for car sales will depend on the technology selected. If the hybrid electric
technology is selected, Future Cars Inc. will capture nine percent of the U.S. market demand. If
the fuel-cell technology is chosen, the company will capture 12 percent of the U.S. market
demand because only one other car maker (Honda) currently offers a fuel-cell car.
The Marketing analysts have determined that Future Cars
￿
Total Direct Materials
This value is the total amount of money spent on direct materials to
produce the forecasted number of cars that Future Cars will sell each year. If the electric hybrid
is selected in the Inputs section, the Total Direct Materials is the direct materials for each
electric hybrid car (from the Constants section) multiplied by the Forecasted Sales for Future Cars.
If the fuel-cell technology is selected in the Inputs section, the Total Direct Materials is the
direct materials for each fuel-cell car (from the Constants section) multiplied by the Forecasted
Sales for Future Cars.
￿
Total Direct Labor
This value is the total amount of money spent on direct labor to produce
the forecasted number of cars that Future Cars will sell each year. If the electric hybrid is
selected in the Inputs section, the Total Direct Labor is the Direct Labor rate multiplied by the
Direct Labor Hours for each electric hybrid car (both values are in the Constants section),
multiplied by the Forecasted Sales for Future Cars. If the fuel-cell technology is selected in the
Inputs section, the Total Direct Labor is the Direct Labor rate multiplied by the Direct Labor
Hours for each fuel-cell car (both values are in the Constants section), multiplied by the
Forecasted Sales for Future Cars.
￿
Total Manufacturing Overhead—This value is the total amount of money spent on manufacturing
overhead to produce the forecasted number of cars. If the electric hybrid is selected in the
Inputs section, the Total Manufacturing Overhead is the Direct Labor Hours required for each
hybrid car multiplied by the Overhead Allocation Rate (both values are in the Constants
section), multiplied by the Forecasted Sales for Future Cars Inc. If the fuel-cell technology is
selected, the Total Manufacturing Overhead is the Direct Labor Hours required for each fuel-cell
car multiplied by the Overhead Allocation Rate (both values are in the Constants section),
multiplied by the Forecasted Sales for Future Cars Inc.
￿
Cost of Goods Sold
This value is the sum of the Total Direct Materials, Total Direct Labor, and
Total Manufacturing Overhead required to produce the cars.
 
Search JabSto ::




Custom Search