BUS 503 Accounting Information for Decision-Making Computer Homework Assignment #1 Cost of Goods Manufactured / Decision Model Winter 2015 The objectives of this assignment are to: a) enhance your understanding of how product costs are accumulated and how they impact the companys 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 companys net income: Action 1: Purchase additional factory equipment which will increase annual depreciation by $30,000. Action 2: Reduce the number of production supervisors which will result in an indirect cost savings of $35,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 15% Event 2: 50% probability that sales revenues will decrease by 10% Keep in mind that your decision model must incorporate the following elements: Companys 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 companys net income (often referred to as the companys 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 60% and 40% to the two events, respectively. Alternative case 2: Assign probabilities of 30% and 70% to the two events, respectively. BUS 503 Accounting Information for Decision-Making Assignment #1 - Cost of Goods Manufactured / Decision Model Winter 2015 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. Business memorandum (One page only) 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 Saturday, January 17, 2015. At the start of class on Saturday, January 17, 2015, you will provide the professor with a paper copy of your business memo and spreadsheet. No late work will be accepted. Grade and Score Sheet 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: Business memo (50%) Decision model design (20%) Spreadsheet content: (30%) 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: Business memorandum 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) (Data fields, calculations, headings, page numbers, case numbers, etc.) Spreadsheet content Complete and accurate Original data case ___________________ (10 points) Alternative Case 1 ___________________ (10 points) Alternative Case 2 ___________________ (10 points) TOTAL SCORE __________________ (100 points)