Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
￿
Manufacturing Cost per Unit
This value is the Cost of Goods Sold divided by the Forecasted
Sales for Future Cars Inc. This data is not required, but managers frequently want to see the
actual cost of manufacturing for their products so they can try to reduce costs.
￿
Unit Sales Price
s suggested retail price (MSRP). Marketing has
developed a formula to set this price according to the following situations:
This value is the manufacturer
￿
If the economic outlook is for a recession and the gasoline price is low, the sales price will be
the same as the previous year
s price (no increase).
￿
If the economic outlook is for a recession and the gasoline price is high, the sales price will
be two percent higher than the previous year
s sales price (in other words, the previous
year
s sales price multiplied by 102 percent).
￿
If the economic outlook is for a boom and the gasoline price is low, the sales price will be
four percent higher than the previous year
s sales price.
￿
If the economic outlook is for a boom and the gasoline price is high, the sales price will be
six percent higher than the previous year
s sales price.
(Hint: To set these prices, you will have to use a nested IF statement that contains AND
functions.)
￿
Period Costs
These values are all the other nonmanufacturing costs incurred with running the
new technology, including product improvement, marketing, distribution, and customer service
costs. In this case, the Period Costs are roughly the same regardless of which technology is
chosen, and are not dependent on the volume of cars produced. However, the costs do depend on
the Economic Outlook (from the Inputs section). If the Economic Outlook is for a recession, the
Period Costs are three percent higher than the previous year
s Period Costs. If the Economic
Outlook is for a boom, the Period Costs are six percent higher than the previous year
s Period
Costs.
The Calculations section includes several complicated formulas. If you get lost while trying to write the
nested IF and AND formulas, refer back to Tutorial C or ask your instructor for help.
Income and Cash Flow Statements Section
The statements for income and cash flow start with the cash on hand at the beginning of the year. Because
Future Cars Inc. is funding the capital investment internally—
you will
have to deduct the invested funds from the cash on hand at the end of the year 2012. Figure 6-5 and the
following list show how you should structure the Income and Cash Flow Statements section.
that is, with its own cash on hand
FIGURE 6-5
Income and Cash Flow Statements section
￿
Beginning-of-year Cash on Hand
For 2013, this value is the End-of-year Cash on Hand from
2012 minus the capital investment, depending on the technology chosen. If you choose the
hybrid electric technology, the capital investment will be \$600 million (cell B10 in the Constants
section). If you choose the fuel-cell technology, the capital investment will be \$1.1 billion (cell
B11 in the Constants section). For 2014 and 2015, the Beginning-of-year Cash on Hand will be
the End-of-year Cash on Hand from the previous year.

Search JabSto ::

Custom Search