BUS 503 Accounting Information for Decision-Making

Computer Homework Assignment #1 – Cost of Goods Manufactured / Decision Model

Fall 2014

The objectives of this assignment are to:  a) enhance your understanding of how product costs are accumulated and how they impact the company’s net income; b) develop your skills in developing a decision model utilizing Excel spreadsheet software; and c) improve your ability to interpret the results and communicate them in writing and verbally.

Decision Model (Original data case)

You are building a decision model for Superior Metals Corporation based on the data given in Problem #3-49 on page 120 in your textbook (omit part 4).  This includes a schedule of cost of goods manufactured, a schedule of cost of goods sold, and an income statement. In addition to the given data, you learn through interviews with Company management that they are considering taking one of the following two courses of action in order to improve the company’s net income:

Action 1:  Purchase additional factory equipment which will increase annual depreciation by \$35,000.

Action 2:  Reduce the number of production supervisors which will result in an indirect cost savings of \$40,000

Also, you are to assume the following events and the likelihood of the following two events occurring:

• Event 1:  50% probability that sales revenues will increase by 18%
• Event 2:  50% probability that sales revenues will decrease by 12%

Keep in mind that your decision model must incorporate the following elements:

• Company’s criterion for selecting the best alternative action.
• Alternative actions that are under consideration by Company management.
• Relevant events that can happen and the likelihood of each of these events occurring.
• Possible outcomes given the various actions and events.
• Expected value of each possible outcome.

Sensitivity Analysis (Alternative cases 1 and 2)

Once you build your decision model based on the original data case, then you are to conduct a sensitivity analysis. The objective of this sensitivity analysis is for you to gain a deeper understanding of how costs impact the company’s net income (often referred to as the company’s bottom-line) by processing two alternative cases through your decision model.  This involves changing the probabilities that the events will occur.  The two alternative cases are as follows:

• Alternative case 1:  Assign probabilities of 40% and 60%   to the two events, respectively.
• Alternative case 2:  Assign probabilities of 70% and 30%   to the two events, respectively.

BUS 503 Accounting Information for Decision-Making

Assignment #1 – Cost of Goods Manufactured / Decision Model

Fall 2014

Page 2 of 3

Design your spreadsheets so that the probabilities are located in an “input parameters” section.  When you use the probabilities, you will reference the cells in which they are located rather than entering them directly into the spreadsheets.  For all other data fields, you will enter them directly into the spreadsheets or if they already exist in other cells, then simply reference them rather than re-entering them into the spreadsheets.

Your spreadsheet printouts must include grid lines and row/column labels. Include all of your spreadsheets in one file by setting up separate sheets for the original data case and the two alternative cases in the normal view.

Prepare a one-page business memorandum for the president of Superior Metals Corporation. Your memorandum will describe your decision model and explain what the results mean to the Company.

Required Format and Labeling

• For the one-page business memo, use Word, Times New Roman font size 12 and single line spacing.
• For the spreadsheets, use Excel, Times New Roman and at least font size 10.
• Type your full name in the upper right corner of every page of the memo and spreadsheets.
• Show all your calculations and make sure all pages and fields are properly labeled.
• Utilize the Sheets function in Excel so that your original data case, case 1 and case 2 are set up as separate sheets (3) within the same file and labeled accordingly.

Due Date

Your decision model (spreadsheet) and business memo is due on Tuesday, September 9, 2014 at 12:30 p.m.

At the start of class on Tuesday, September 9, 2014, you will provide the professor with a paper copy of your business memo and spreadsheet.

No late work will be accepted.

This is an individual assignment and must be your own work.  The assignment is worth 15% of your course grade as indicated in the syllabus.  The grade for this assignment comprises the following:

• Decision model design (20%)

BUS 503 Accounting Information for Decision-Making

Assignment #1 – Cost of Goods Manufactured / Decision Model

Fall 2014

Page 3 of 3

Here is the score sheet:

Content:                                                          ___________________ (30 points)

Description of decision model

Interpretation of what the numbers mean

Organization                                                   ___________________ (10 points)

Proper language                                              ___________________ (10 points)

Decision model design

Design is complete and logical                       ___________________ (10 points)

Parameter-driven                                             ___________________ (5 points)

Proper labeling                                                ___________________ (5 points)

page numbers, case numbers, etc.)

Complete and accurate

• Original data case                               ___________________ (10 points)
• Alternative Case 1                              ___________________ (10 points)
• Alternative Case 2                              ___________________ (10 points)

TOTAL SCORE                                          __________________ (100 points)