Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
offer the best strategic opportunity for the firm. The department managers have been asked to provide the
following data from their functional areas:
￿
Financial and Accounting
The current cash position of the company, the cash outlay for the
two investment choices, data for manufacturing costs and period costs for each alternative, and
the corporate income tax rates
￿
Sales
Forecasts for U.S. sales of alternative-energy cars, a formula for calculating the sales
demand for each technology alternative, the effect of gasoline prices on the sales demand, and
the projected market pricing for alternative-energy cars
￿
Operations
Labor hours required for manufacturing cars of either technology
Specifically, the departments have given you the following data:
￿
Direct labor costs per hour, 2013 through 2015
￿
Direct materials costs per hybrid or fuel-cell vehicle, 2013 through 2015
￿
Direct labor hours per hybrid or fuel-cell vehicle, 2013 through 2015
￿
Manufacturing overhead allocation rates per direct labor hour, 2013 through 2015
￿
Capital investment for electric hybrid and fuel-cell cars (end of 2012)
￿
Median alternative-energy car sales price for 2012 (market price)
￿
Period costs (2012)
￿
End-of-year cash on hand (2012)
This money is available for investment.
In addition, the Assignment 1 section contains information you will need to write the formulas for the
Calculations section, Income and Cash Flow Statements section, and IRR Calculation section.
You will use Excel to see how much profit and positive cash flow the two alternatives will generate for
Future Cars for the next three years, and you will use Excel to calculate an internal rate of return for each
alternative. You will also examine the effects of the economy (recession or boom) and the price of gasoline
(low or high) on your projected vehicle sales and profits for each alternative. In summary, your DSS will
include the following inputs:
1. Your decision to invest in either the hybrid electric or fuel-cell technology
2. Whether the economic outlook is for a recession or boom cycle
3. Whether the price of gasoline is low or high
Because Future Cars is entering a market that already has substantial competition, the company cannot
set the price for its automobiles simply by marking up the cost by a certain percentage. The company knows
the market price for alternative-energy cars and must make its prices competitive. The new fuel-cell
technology will allow Future Cars to price its fuel-cell cars slightly higher than the electric hybrid models, but the
company knows it is still competing against well-established competitors with good products.
Your DSS model must account for the effects of the preceding three inputs on costs, selling prices, sales
demand, and other variables. If you design it well, your model will let you develop
scenarios with all
the inputs, see the results, and show a preferred alternative for Future Cars management to adopt.
what-if
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
In this assignment, you will create a spreadsheet to model the business decision that Future Cars Inc. is
seeking. In Assignment 2, you will write a report to the CEO about your analysis and recommendations. In
Assignment 3, you will prepare and give a presentation of your analysis and recommendations.
You will start by creating the spreadsheet model of the company
s financial and marketing data. The
model will cover three years of sales (2013 through 2015) for the new technology selected. Assume that the
preliminary research and development was completed in 2011 and 2012, and that engineering and
manufacturing assets are in place to begin production and sales in 2013.
This section will help you set up each of the following spreadsheet components before entering the cell
formulas:
￿
Constants
￿
Inputs
￿
Summary of Key Results
 
Search JabSto ::




Custom Search