Spreadsheet Project
Spring 2023
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 40,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 $326,000 but would increase sales volume by 4,500 units.
(h) Reduce the selling price of the product by $12.00 per unit. This would increase sales volume by 14,600 units.
(i) Write a memo, explaining whether the company should use any of the suggestions, and make recommendation about which suggestion is the optimal. 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, Variable cost, 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.
The grading rubric is as follows:
Excel skills Accuracy of scenario analysis Two memos
1)Inputting data and writing formula using cell references (absolute and relative references) (5’);
2)Generating CVP graph with relevant data (5’) 1)The correctness of parameters (5’);
2)The accuracy of contribution margin income statement including the operating income (5’);
3)The contribution margin per unit, the contribution margin percentage, the break-even units, and the break-even revenue (5’). 1) For the scenario analysis memo, identify the problem, apply relevant concepts, use relevant evidence (data), and provide logical conclusion (12’)*;
2)For the CVP memo, derivation of three pieces of information from the CVP graph (3’) .
*The following is the grading rubrics for the memo of scenario analyses (your critical thinking skill is evaluated here): You are expected to identify the problem, apply relevant concepts, use relevant evidence, and provide logical conclusions in this memo of scenario analyses. You will be graded by how you achieve each objective, from unacceptable, acceptable, to excellent, you will get 1, 2, and 3 points, respectively. For example, for the objective of “use relevant evidence”, you will obtain 2 points if your work is acceptable; for the objective of “provide logical conclusions”, you will obtain 3 points if your work is excellent.
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.
