Accounting Spreadsheet project
ACCT 5131 Spreadsheet Project Summer 2019 Academic Honesty You must work on this project individually. You are not to ask anybody for assistance other than Professor Yi 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. 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 with this file of instructions. 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 with this file of instructions. 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.’ Requirements The model parameters for the baseline case are entered in the Excel spreadsheet, which I have uploaded with this assignment. 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. Just add to the file that you download. Failure to use this file will result in a 10 point deduction (out of 40 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 79, Exhibit 3-1 of the Datar and Rajan textbook and in the Chapter 3 (Datar and Rajan) course notes. Create this statement below the baseline case parameters in the Excel file you download. Key in proper headings. (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 curser 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. Rename your new sheet to indicate the name of the new scenario. For example, you could name the sheet for (e) ‘Commission.’ To rename a sheet, right click on the sheet name. Select “Rename.” Key in the new name. (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 $4.50 per unit. Sales would increase by 44,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 $36,000. (g) Launch a new advertising campaign. This would increase fixed advertising expense by $348,000 but would increase sales volume by 4,800 units. (h) Reduce the selling price of the product by $15.00 per unit. This would increase sales volume by 16,800 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). 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 link. 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.8 points Part 2.e.4 points Part 2.f.4 points Part 2.g.4 points Part 2.h.4 points Part 2.i. (memo recommending course of action)4 points Part 3.j. (graph)8 points Part 3.k. (memo based on graph)4 points Total40 points Please see the syllabus for point deductions for late submissions. Submitting Your Assignment Submit an electronic copy of your assignment with the course link ‘Spreadsheet Assignment.’ Be sure you are finished with your assignment when you upload your files, as you cannot resubmit it. You can upload more than one file. 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. 2 Baseline Case Your Name:Key Your Course:ACCT5131 Date:22-Aug-19 PARAMETERS FOR BASELINE CASE The following numbers are estimates for the upcoming year for a manufacturing company. Since the company is effective at implementing a JIT inventory system, assume there is no beginning or ending inventory. No. of units sold120,000 Selling price per unit$240.00 Fixed ExpensesVariable Expenses (per unit sold Production costs: Direct materials$18.00 Direct labor36.00 Factory overhead$2,160,00024.00 Marketing expenses: Sales salaries and commissions540,0007.50 Advertising360,000 Miscellaneous mktg. expenses108,000 Administration expenses: Office salaries720,000 Supplies105,0001.50 Miscellaneous admin. expenses72,000 TOTAL EXPENSES$4,065,000$87.00