Create a income statement and manipulate it on different tabs using the excel sheet that was created.
ACCT 3333 Spreadsheet Assignment Spring 2020 Academic Honesty You must work on this project individually. You are not to ask anybody for assistance other than the instructor of the course or a tutor at the University of Houston-Clear Lake Student Success Center. Failure to adhere to the academic honesty rules for this assignment will result in a score of zero. Purpose of Assignment This project will give you experience in designing and implementing a spreadsheet to assist you in making decisions. Estimated Time to Complete This assignment will likely require three to four hours for a basic to intermediate spreadsheet user. Location of Relevant Assignment Material Please see the folder ‘Spreadsheet Assignment’ in the ‘Content’ area of the course Blackboard page. Guidelines Please read the guidelines in the “Identifying Elements of a Financial Model” section in Chapter 12 of the Hilton (2008) textbook (now out-of-print). I have uploaded Chapter 12 of that textbook in the spreadsheet assignment folder. I will deduct points if you do not follow the advice that “…parameters should be located and clearly identified in a defined parameter or input-data area of the spreadsheet” and “…the formulas in the analysis sections should never contain the actual numerical values of the parameters. Instead use the parameters’ cell locations in all formulas where they occur” (Hilton, 2008, pages 476 and 477). To help you adhere to these guidelines, I have started your Excel solution file and created a ‘Parameters’ section for you. That file is uploaded in the spreadsheet assignment folder. Spreadsheet Format Each sheet of your Excel file should include your name, the course number, and the date. Place this information at the top, either in a header or in the first few rows. Label each sheet of your workbook so it clearly identifies the sheet. I have labeled the first sheet as ‘Baseline Case’ and offered labels of other worksheets, but if you want to use other labels for your worksheets, please do so. Requirements The model parameters for the baseline case are entered in the Excel spreadsheet, which is in the spreadsheet assignment folder. The sheet for the baseline case is named ‘Baseline Case.’ Download that Excel file and complete your assignment using that file. Do not create a new Excel file. Failure to use this file will result in a 10 point deduction (out of 50 points for the assignment). Use your last name to rename the file I have uploaded. If you “mess up” and need to start over, download the Excel file again. The assignment requires you to manipulate the baseline case model parameters in three different ways: 1. Complete a CVP analysis for the baseline case; 2. Complete four other scenarios (i.e., what-if analyses), and recommend the best scenario; and 3. Prepare a graph and derive information from the graph. 1.Make CVP Calculations for the Baseline Case On the first sheet of your workbook (the sheet labeled ‘baseline case’), do a-d below. (a) Prepare a contribution margin income statement (also called a variable-costing income statement) for the manufacturing company for the upcoming year. Examples of contribution income statements are on page 69, Exhibit 3-1 of the Datar and Rajan textbook and in the Chapter 3 course notes. Create this statement below the baseline case parameters in the Excel file you download. Key in proper headings. Check figure: Operating profit (operating income) of $351,000. (b) Compute the company’s contribution margin per unit and contribution margin percentage for the upcoming year. The contribution margin percentage is calculated as contribution margin per unit/selling price per unit or as total contribution margin / total revenue. Make these calculations below your income statement. Clearly label these calculations. (c) Calculate the company’s breakeven point in units for the upcoming year. Make this calculation below your contribution margin calculations. Use Excel’s “round” function to round up to the nearest whole number. To do this, move your cursor to the cell beside the decimal number and key in the following formula: =ROUNDUP(cell reference,0). The italicized cell reference means you need to key in the cell where the decimal number is located (e.g., F12). The number 0 means zero decimal places. Clearly label this calculation. (d) Calculate the company’s breakeven point in sales dollars for the upcoming year. Make this calculation below your breakeven calculation in units and use the company’s contribution margin percentage to make this calculation. Clearly label this calculation. 2.Complete Four Scenarios (What-if Analyses) You want to determine whether the following four suggestions (i.e., e, f, g, h) would improve the company’s performance. Determine the effects of each suggestion on operating income, contribution margin per unit, contribution margin percentage, breakeven point in units, and breakeven point in sales dollars. Calculate the effects of each suggestion independently of the other suggestions. In other words, use the original baseline case data and make the first change (e); use the original baseline case data and make the second change (f); and so on. However, do not overwrite the original baseline case. The easiest way to do this is to copy the original data to a new sheet and then replace the original data parameters. To copy a sheet, click on the sheet name. Select “Move or Copy.” Click on the “Create a copy” box. Click OK. (e) Put all personnel on commission. This action would affect the sales salaries and commissions expense by eliminating the fixed portion and increasing the variable portion by $7.50 per unit. Sales would increase by 12,000 units. (f) Redesign the package for the product. This would decrease the variable direct materials cost by $1.50 per unit but would increase the fixed factory overhead by $40,000. (g) Launch a new advertising campaign. This would increase fixed advertising expense by $360,000 but would increase sales volume by 4,500 units. (h) Reduce the selling price of the product by $15.00 per unit. This would increase sales volume by 5,200 units. (i) Write a memo, explaining whether the company should use any of the suggestions. Create your memo in Word, not Excel. Use proper memo format. Word has a memo template (FILE | NEW | type in ‘memo’ in the ‘search for online templates’ box). Writing Tips: · Write a memo as if you write to your supervisors to discuss possible strategic options available to them. · When you make a recommendation, explain your reasoning clearly using your analyses. · Make sure your numbers, spelling, and grammar are correct and consistent. · Be concise (quality over quantity). · The logic of your answers should be easy to follow. 3.Prepare a Graph and Derive Information from the Graph (j) Prepare a cost-volume-profit (CVP) graph using only the baseline case. Below are some instructions for creating a graph. For detailed instructions, download the instructions that are available to you in the spreadsheet assignment folder. You need to first prepare a table with your data so that you can instruct Excel to graph the table. You will need the following columns: Units, Revenue, Fixed Costs, and Total Costs. In Excel, the type of chart you need to select is the “scatter.” This makes both axes what Excel calls “value” axes. Since both x and y-axes have values, you will be able to control the tick marks on both axes. The graph should be readable and easy to interpret. For example, showing the number of units on the graph in increments of 100,000 does not give enough detail. Showing the number of units in increments of 100 gives too much detail. (k) Write a memo stating at least three pieces of information you can derive from reading the graph. For example, one item of information could be that if the manufacturing company sells 20,000 units, the revenue is approximately $_____. Create your memo in Word, not Excel. Create this memo in the same Word file you used for the memo in 2. (i). Grading Your grade will be based on the correctness and format of your spreadsheet. I will deduct points for failure to follow instructions or if the spreadsheet or graph is difficult to follow. Also, on this assignment, I count off for carry-through errors. After all, this is a spreadsheet that would be used by a business. The grading rubric is as follows: Parts 1.a. through 1.d.10 points Part 2.e.5 points Part 2.f.5 points Part 2.g.5 points Part 2.h.5 points Part 2.i. (memo recommending a course of action)7 points Part 3.j. (graph)10 points Part 3.k. (memo based on graph)3 points Totalof 50 points Please see the syllabus for point deductions for late submissions. Submitting Your Assignment a) Electronic submission: You have to submit two separate files: 1) An excel file that includes what-if analysis and CVP graph 2) A word file that includes two memos (what if analysis and CVP graph) Then submit them using the course link ‘Spreadsheet Assignment,' which is on the left-hand side of the course Blackboard page. Be sure you are finished with your assignment when you upload your files, as you cannot resubmit it. After uploading your files, be sure to press the “Submit” button. Due to potential technical difficulties, it is not a good idea to submit your assignment right before the deadline. b) In-class submission: Additionally, you have to submit a hard copy of your assignment that includes both excel print-outs and memos. In-class submission should start with a cover page that shows course name, current semester and year, and your name. When you prepare the hard copy, please follow the following order of documents. 1. Cover page 2. Baseline case (excel print out) – Try print it out within a page 3. Commission case (excel print out) – Try print it out within a page 4. Redesign case (excel print out) – Try print it out within a page 5. Advertising case (excel print out) – Try print it out within a page 6. Selling price case (excel print out) – Try print it out within a page 7. Memo on what if analysis 8. CVP graph (excel print out) – Try print it out within a page 9. Memo on CVP graph Please make sure to staple it (top left corner. Do not use a paper clip), but you don’t need to put it in a fancy folder. For