Spreadsheet assignment: Income Analysis

This assignment is based on an assignment in the book: Miller, L. (2008) MIS Cases: Decision Making with Application Software (4th Edition). Upper Saddle River, New Jersey: Prentice Hall.

Your task for this assignment is to help a small start-up business analyze its performance over the past year and provide an analysis worksheet to help answer some what-if questions for future years.

This problem uses a number of advanced features of Excel and it is expected that there will be questions. Keep in mind that the CIS 123 website has a FAQ sheet for using Excel, there will be a demonstration of Excel in one of the optional class sessions, and there are many tutorials on how to use specific features in Microsoft Excel on the Web. This is particularly useful if you want a question answered quickly outside of class time and you don't have an Excel reference at hand.

Starting documents are available at: http://kermit.kishwaukeecollege.edu/~dklick/cis123/customAssignments/fetchIncomeAnaysis.html. NOTE: If you have taken this class previously and the assignment is the same, your starting document is DIFFERENT even though it may appear similar. Start over by downloading the new starting document.

There are sample pictures linked at the end of this document that show what most of the project should look like when you are finished. Keep in mind that many cells must be formulas and that typing in numbers to match a picture instead of using a formula does not fulfill the requirements.

Note: One common mistake is to misuse functions such as SUM. If you want to add a contiguous group of cells, such as D4 through D10, then SUM is perfect since you can specify the range and it will automatically adjust if a row is inserted or deleted within that range. The formula would look like: =SUM(D4:D10). That is much clearer and better than the error-prone: =D4+D5+D6+D7+D8+D9+D10. The misuse comes when people try to use SUM for everything. If you just want to add cells E7 and E12 use: =E7+E12. If you want to multiply them use: =E7*E12. Do NOT use =SUM(E7+E12) or =PRODUCT(E7*E12). In those cases SUM and PRODUCT are not doing anything other than wasting computer processing power. If you do not understand this, please ask the instructor for clarification. Points will be deducted for misusing the SUM and PRODUCT functions.

Main Tasks

Create an income statement section using formulas
Create a section for displaying important ratios using formulas
Create a two dimensional data table to analyze how price and cost relate to profit
Create an exploded 3-D pie chart of fixed costs
Create and compare different scenarios
Use the Solver add-in to generate a solution

Worksheets

IncomeAnalysis: where most of the data and formulas are entered
PriceCostAnalysis: contains a two-variable data table
FixedCostChart: contains the 3-D pie chart of fixed costs
Scenario Summary: contains the results of the Scenario tool (created by Excel)
Answer Report 1: contains the results of the Solver tool (created by Excel)

Requirements

You probably want to have the sample IncomeAnalysis worksheet viewable as you create your version.
Do NOT work closely together or copy or share work with other students. The penalties can be quite severe. You are welcome to ask all the questions you want and to seek help from the instructor or LSC tutors.
Each student will get their own Excel workbook as a starting document ready to be modified. Use the document sent to you and not a copy of someone else's as all of the documents are slightly different. If you turn in a document that is not the one sent to you, then it will not be awarded any points. Your starting document is available at: http://kermit.kishwaukeecollege.edu/~dklick/cis123/customAssignments/fetchIncomeAnalysis.html.
Change the name on the first worksheet from Sheet1 to IncomeAnalysis (1)
The "Input Section" is where data can be entered to control the rest of the workbook. That data has already been entered for you. You may want to refer to the sample documents to reset values if you accidentally mess them up.
There should not be any formulas or values in any cell that is not specified as having a formula or value in it.
You will have to create formulas for the Income Statement section
1. Sales (B23): should be how many items were sold multiplied by the price (1)
2. Variable expenses (B25:B30): these are the total variable expenses; each one must be the variable expense per unit times the number of units sold (3)
3. Total variable expenses (B31): the sum of the variable expenses just calculated (1)
4. Contribution margin (B32): sales minus total variable expenses (1)
5. Fixed expenses (B34:B38): these are just copies of the fixed expenses from the input section, but they must be formulas with cell references so they change automatically if the corresponding cell in the input section changes (2)
6. Total fixed expenses (B39): the sum of the fixed expenses (1)
7. Operating income (B40): the contribution margin minus total fixed expenses (1)
8. Income taxes (B41): operating income multiplied by the income tax rate; make sure both are references to the correct cells (1)
9. Net income (B42): operating income minus income tax (1)
You will have to create formulas for the Ratios section
1. BEP (B45): fixed costs divided by (revenue per unit minus total variable cost per unit) (2)
  Note: BEP stands for break-even point. That tells you how many units you have to sell in order to break even. The break-even formula uses total variable costs PER UNIT, not the total variable costs overall. You can get that number a couple of ways. One is to use the SUM function to add up the variable costs per unit.
2. BEP with target income (B46): (fixed costs plus target income) divided by (revenue per unit minus total variable cost per unit) (2)
3. Contribution margin ratio (B47): (sales - total variable costs) divided by sales (2)
4. Operating margin ratio (B48): operating income divided by net sales (2)
5. Net margin ratio (B49): net income divided by net sales (2)
Create a chart for fixed costs
1. See the sample document to see how the chart is formatted.
2. One of the easiest ways to get a chart placed on a new worksheet is to create the chart on the same worksheet as the data, click on its edge to select it, right click and select cut, click on the tab of the worksheet you want the chart on, and then paste the chart onto the new worksheet.
3. Create a 3-D pie chart that shows how fixed costs are divided. The chart must be based on the data in the IncomeAnalysis worksheet - NOT a copy of that data. (1)
4. Add the title "2014-2015 Fixed Costs" in 18 pt font. (1)
5. Add data labels to the chart showing the amounts and percentages. (2)
6. Leave the legend showing.
7. Place the chart on a new worksheet and rename the worksheet: "FixedCostChart" (1)
8. An example of the FixedCostChart worksheet is available to show how this chart looks.
Create a two dimensional data table to analyze how price and cost relate to profit
1. Switch to the PriceCostAnalysis worksheet.
2. Several values, formulas, and cell formatting are already completed. Note how text can be turned sideways to help with the formatting. There are formulas in cells C2, C5, and C6. The demand is calculated using a simplified demand power curve, where higher prices lead to fewer purchases being made (just like real life).
3. Names have also been defined for cells C1 through C6 that you may use in writing your formula, or you may just use cell references in your formula if you wish - but your formula is only allowed to reference cells on the same page in the C1:C6 range. You must create a formula in cell B10 that calculates the profit. You can calculate profit by subtracting total fixed and variable costs from the revenue. Given the values that you started with, the result should be 5,650.86. (1)
4. Create a data table in cells B10 through I25. Since this is a two dimensional data table, you will need to fill in references for both the row and column input cells. What Excel is asking you is which cell in the C1:C6 range is being represented by changing values in the top row and leftmost column of the data table. If the top row represents total variable cost, then you would select cell C6 as the row input value. You should get the same results as the example. (3)
5. Add conditional formatting to each column within the data table individually. The format should display the top value with a light green background and dark green text. That will highlight the optimum for profit. For example, if the unit cost is $350, then we can quickly look down that column and see that the predicted optimum profit is when the product is priced at $700. (3)
6. See the sample PriceCostAnalysis worksheet
Create a scenario summary report
1. Excel creates the summary report on a new worksheet automatically when you run the scenario manager. Do NOT try to create the scenario summary report worksheet by hand. That would be a lot of work and isn't what we want.
2. You have an example of the Scenario Summary worksheet available to see how you are doing.
3. Use the scenario manager (in What-if-analysis on the Data tab).
4. You will be creating two different scenarios to see how specific changes to units sold, revenue per unit, and labor cost will effect net income. The scenario summary will also compare these two scenarios against the current model.
5. The first scenario (Scenario 1) should change the units sold to 200, the revenue per unit to 950, and the labor per unit to 150 and see what the result is on net income. (2)
6. The second scenario (Scenario 2) should change the units sold to 100, the revenue per unit to 1650, and the labor per unit to 200 and see what the result is on net income. (2)
7. Create a scenario summary report (which should appear on a separate worksheet named Scenario Summary). (2)
8. Change the cells in column C of the scenario summary report to show the correct description rather than cell references, and modify the widths of columns B and C to better fit the new descriptions. (2)
Use the Solver add-in to answer a question
1. Excel creates the answer sheet (Solver) report on a new worksheet automatically when you run Solver. Do NOT try to create this worksheet by hand. That would be a lot of work and isn't what we want.
2. You have an example of the AnswerReport1 worksheet available to see how you are doing. The numbers on the generated report may vary slightly depending upon the version of Excel being used and the internal modeling technique Excel is using.
3. You will probably have to add the Solver add-in to Excel using the File tab (or Office button in Office 2007), Excel options, and adding Solver as an add-in.
4. Solver should be available on the far right of the ribbon when you click on the Data tab.
5. We want the net margin ratio (the "objective" or "target" cell) to be equal to .15 (1)
6. We want Solver to do this by changing the cells for units sold and revenue per unit. (2)
7. Solver should be given the constraint: Total variable expenses less than or equal to $110,000. (1)
  Note: You do NOT enter in $ signs or commas into Excel when entering numbers. In this case, just: 110000
8. Solver should be given the constraint: Total units sold less than or equal to 250. (1)
9. Solver should be given the constraint: Revenue per unit less than or equal to $1100. (1)
10. Generate an answer report on a separate worksheet (named Answer Report 1). Be sure to select the options to restore the original values and to create an Answer report. (1)

Examples

There are 50 points total available for this assignment. Remember that there is a discussion forum available for asking questions about this assignment. There is also an Excel FAQ page available on the CIS 123 web site. Class is also a great time to demonstrate Excel usage questions, so feel free to bring in questions to a class session.