Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
￿
Manufacturing employee salary
The average manufacturing employee salary is expected to
increase 2% per year in each of the next three years. Format the value as currency with no
decimal places.
Income and Cash Flow Statements
The forecast for net income and cash flow starts with the cash on hand at the beginning of the year, continues
with the income statement, and concludes with the calculation of cash on hand at year
s end. For readability,
format cells in this section as currency with zero decimal places. You must compute values by cell formula;
use hard-coded numbers in formulas only when instructed. Cell formulas should not reference a cell that
contains a value of
Your spreadsheets should look like those shown in Figures 7-5 and 7-6. A discussion of
each item in the section follows each figure.
NA.
FIGURE 7-5
Income and Cash Flow Statements section
￿
Beginning-of-year cash on hand
This amount is the cash on hand at the end of the prior year.
￿
Revenue
This amount is a function of the expected units sold in the year and the average
selling price for the year. Both of these values are taken from the Calculations section.
￿
Manufacturing employee salary
This amount is a function of the number of manufacturing
employees in the year and the average manufacturing employee salary expected in the year.
Both of these values are taken from the Calculations section.
￿
Raw material costs
This amount is a function of the expected units sold in the year and the
expected snap costs and strap costs in the year. All of these values are taken from the
Calculations section.
￿
Fixed costs
This amount is a constant that can be echoed here.
￿
Health care cost
s health care cost per employee (a
constant) and the total number of employees to be covered.
This amount is a function of the year
￿
Total Costs
This amount is the total of manufacturing salaries, raw material costs, fixed costs,
and health care costs.
￿
Income before interest and taxes
This amount is the difference between revenue and total costs.
￿
Interest expense
This amount is the product of the debt owed to the bank at the beginning of
the year and the interest rate for the year (a constant).
￿
Income before taxes
This amount is income before interest and taxes minus interest expense.
￿
Income tax expense
This amount is zero if income before taxes is zero or less. Otherwise,
income tax expense is the product of the year
s tax rate (a constant) and income before taxes.
￿
Net income after taxes—This amount is the difference between income before taxes and income
tax expense.
Line items for the year-end cash calculation are discussed next. In Figure 7-6, column B represents 2011,
column C is for 2012, and so on. Year 2011 values are NA, except for End-of-year cash on hand, which is $25,000.
 
Search JabSto ::




Custom Search