Microsoft Office Tutorials and References
In Depth Information
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
The companies that make your snaps and fabric pass along changes in their costs, meaning that a change
in the price of petroleum will affect the cost of the snaps and fabric. In recent years, world oil prices have
fluctuated greatly. Many economists and petroleum experts think that the price of oil will increase
significantly in this decade, which makes you nervous about cost control in the next few years. On the other
hand, some experts think that oil prices will not change much, and might even decline.
The price of oil is commonly quoted by the barrel. In 2011, the average price of a barrel of oil on the
open market was \$80. Big Dog
s petroleum-based raw material costs are about 50% of the percentage change
in the price of a barrel of oil. For example, if the price of oil rose from \$80 in one year to \$90 in the next
year, the increase would be 12.5% ((90
80) / 80
.125). Therefore, you would expect Big Dog
s raw
¼
material costs to increase by 6.25% (.5
.125) in the second year.
Even in poor economic times, people are willing to pay for a good specialty product like your dog collar.
continue to increase. You think that unit sales will increase 3% each year for the next three years. Likewise,
you think that you can raise your selling price 3% each year for the next three years. Increasing production
may require adding one or more manufacturing workers.
You also employ an office worker and a janitor/repairman. If you adopt health care as a benefit, you
would cover all your employees. (You would not cover yourself because your wife is a public school teacher,
and her health care plan covers you.) You have identified a basic three-year health care package that would
cost you \$3000 per covered employee in 2012, \$3200 in 2013, and \$3400 in 2014.
Each year, your financial goal is to achieve a 5%
; in other words, you want net income
after taxes divided by total revenue to be at least 5%. If the return is greater than 5%, you conclude that sales
and costs were in control for the year. You can conclude the opposite if the return is less than 5%.
You have achieved more than a 5% return on sales in recent years. You have paid off most of the bank
debt that you used to purchase manufacturing equipment and other fixtures. You have cash in the bank.
Your banker says she would be willing to finance an expansion of the business if necessary, and that the bank
could cover you in lean years.
Looking ahead, you certainly do not want good employees to quit because they are seeking health care
coverage elsewhere. If you pay for health care and oil prices increase, can you still make your profit goal in
the foreseeable future? If oil prices increase, can you make your profit goal only if you do not adopt health
return on sales
health care would be the right thing to do, but making your profit goal is also
important.
Your DSS model needs to account for changes in oil-based costs, with and without the adoption of
employee health care. Your model will let you develop
scenarios with the inputs, see the financial
what-if
ASSIGNMENT 1: CREATING A SPREADSHEET FOR DECISION SUPPORT
In this assignment, you will produce a spreadsheet that models the business decision. Then, in Assignment 2,
you will write a memorandum that documents your analysis and conclusions. In Assignment 3, you will
prepare and give a presentation of your analysis and conclusions to your banker.
First, you will create the spreadsheet model of the financial situation. The model covers the three years
from 2012 to 2014. This section helps you set up each of the following spreadsheet components before
entering cell formulas:
￿
Constants
￿
Inputs
￿
Summary of Key Results
￿
Calculations
￿
Income and Cash Flow Statements
￿
Debt Owed
Return on Sales
A discussion of each section follows. The spreadsheet skeleton is available for you to use. To access the
spreadsheet skeleton, go to your data files, select Case 7, and then select DogCollar.xlsx.
￿

Search JabSto ::

Custom Search