Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
Total Sales Revenues
This value is the Forecasted Sales for Future Cars multiplied by the Unit
Sales Price (both taken from the Calculations section).
Less: Cost of Goods Sold
This value is the Cost of Goods Sold copied from the Calculations
This value is the Total Sales Revenues minus the Cost of Goods Sold.
Less: Period Costs
These values are the Period Costs copied from the Calculations section.
Net Profit before Income Tax
This value is the Gross Profit minus the Period Costs.
Less: Income Tax Expense
If you make a profit (in other words, if the Net Profit before Income
Tax is greater than zero), this value is the Net Profit before Income Tax multiplied by the
Corporate Income Tax Rate from the Constants section. If you make nothing or have a net loss, the
Income Tax Expense is zero.
Net Income after Taxes (Cash Inflow)
This value is the Net Profit before Income Tax minus the
Income Tax Expense. From a strict accounting standpoint, the Net Income after Taxes is not the
cash inflow, because you would have to add back all noncash expenses such as depreciation and/
or depletion to determine the true cash inflow. However, for the purposes of this case, assume
that Net Income after Taxes is equal to cash inflow.
End-of-year Cash on Hand
This value is the Beginning-of-year Cash on Hand plus the Net
Income after Taxes.
Internal Rate of Return Calculation Section
This section, as shown in Figure 6-6, is set up to facilitate using Excel
s built-in IRR (Internal Rate of Return)
Internal Rate of Return Calculation section
The IRR Calculation section includes the following values:
Investment (Cash Outflow)
This value is the investment amount from cell B26 in the
Calculations section multiplied by
1. This investment value must be a negative number to represent it
as a Cash Outflow. (Think of it as money out of your pocket.)
Cash Inflow 2013
This value is the Net Income After Taxes for 2013.
Cash Inflow 2014
This value is the Net Income After Taxes for 2014.
Cash Inflow 2015
This value is the Net Income After Taxes for 2015.
Internal Rate of Return (IRR)
This value is the annual rate of return that your project is
generating for the company. Many companies set a minimum IRR required for a project or investment
to be selected for implementation. To calculate the IRR, click cell B53 (where you want the IRR
result), then click the fx symbol next to the cell-editing window. The Insert Function dialog box
appears (see Figure 6-7). Type IRR in the
Search for a function
text box and then click Go.