Home

1. Read the New York Times and Wall Street Journal articles through once to get the gist of the situation. As you read each article, number the paragraphs for future reference.
2. Read through the articles a second time and identify all of the specific financial information and the relevant amounts [i.e., items for which we have actual numbers]. Open Excel. Set up a grid that looks like the one below. [You will need to adjust the number of rows for each category; the screenshot is simply to illustrate the general structure you will need.]

3. Organize your notes about the information in the articles according to the categories in the example above. Use the cells in the "Description" column for appropriate labels [e.g., "Bowlin's investment" or "Bank charge."] Enter the relevant dollar amounts, and for each item use column C to note the relevant units of measure [monthly annually, per book sold, or whatever]. In column D enter the source [which article] and in column E the relevant paragraph number. These references will enable you to go back to verify the data if you need to. Use the "Other information" category for stuff that doesn't seem to fit elsewhere. The last heading [row 24 above] is for the book-specific information [e.g., para. 9 in the NYT article].
4. After you have identified all the financial information that is given specifically, go back through the article again and find all of the financial information that is implied, but for which specific details are not given. For example, the WSJ article notes that "Mrs. McConville... drew a small salary for evening and weekend work..." [para. 11]. In order to complete our analysis, we'll need to make an assumption about Mrs. McConville's salary. for each such situation, come up with a number that seems reasonable, given the context. [By the way, "salary" implies a fixed amount per week or month, rather than an hourly wage.] In class, we'll go through the assumptions and arrive at a consensus about the "best" number to use, and we'll also not the range of values that we come up with for each parameter. Once we have our model developed, we can test different values to see whether our assumptions would material impact on the outcome.
5. Questions to answer for today's class [after you have done steps 1 - 4]. Do calculations in Excel, using cell referencing. DO NOT ENTER VALUES IN FORMULAS!
   1. Amazon.com's price for Grisham's The Testament is given as $19.57 [NYT article, para. 91]. What is the implicit retail price?
   2. Using the information given, what is Lyle Bowlin's cost?
   3. What is Bowlin's implied sales price?

Part II:

In order to continue with the PY dot com series of assignments, we need to make some assumptions about dates. We'll compress the passage of time simply to make things easier. We'll also assume that all of the investment in the business happens on the same day. When you are finished creating the balance sheets listed in the "Assignment" column below, your worksheet should look something like the example which follows. [You will have to adjust the number of rows.]

Part III:

Refer to the assumptions that we have made about Lyle Bowlin's costs. Which costs are fixed? Which are variable? Remember that cost variability is dependent on the volume of business, not the passage of time. Recall that Bowlin decided to allow each customer to contribute 10% of the profit in their books to the charity of their choice. What impact does the charitable donation have on Bowlin's variable costs? On his contribution margin?

Your assignment:

1. Open a new worksheet in your PYdotcom file. Call the worksheet CVP.

2. Develop a summary of fixed costs, showing the detail and the total, cell referenced from your data worksheet.

3. Develop a summary of variable costs, showing the detail and the total per unit, cell referenced from your data worksheet. You will need two columns for the variable cost summary, one for the "with charitable contribution" scenario and one for the "without charitable contribution" scenario. Compute the weighted average variable cost, based on the assumption that 50% of the people elect to contribute. We'll try other assumptions about the proportion of contributors later.

4. Calculate Bowlin's annual breakeven point in [a] dollar sales, and [b] number of books sold.

5. Suppose it is 3:18 pm on November 9th, 1998. Since November 1, Lyle has sold 997 books, 106 of which were sold today [November 9th]. Lyle wants to know how he is doing. What can we tell him?

6. Develop an income statement [variable costing format] fir Lyle Bowlin's business. Your income statement should be set up so that everything recalculates if we change any of our assumptions.

7. Lyle Bowlin is discouraged by the information we have given him. Suppose he is able to persuade everyone but the students to work for nothing for the first year; hold the student wages at the original amount. Will this act of supreme generosity be sufficient to "save the business'?" What would year 1 net income be if Bowlin's crew agrees to go along with this scheme?

8. Try changing the assumptions for the basic data. For example, increase or decrease Ms. McConnville's salary. Do the changes [within reasonable limits] have a material impact? Try changing other parameters. Irrespective of your knowledge of the "real" outcome to the story, what do you think about the viability of this enterprise? Can this business make money, or is it simply a harebrained scheme whose time has not [and may never] come? ? If it can make money,  how? If not, why not? 

9. Refer to the screenshot below [the choice of rows and columns is totally arbitrary]. Set up a table which calculates the total revenue, total fixed costs, total variable costs, total costs [fixed + variable] and profit at volumes from 0 to 5,000 units, in 500 unit increments. Use the table as the basis for a CVP graph.. Use cell referencing throughout so that you graph will change if we change any of the parameters of the model. You will want to use a scatter plot and then convert the data points to lines. Your graph should look something like the one below. you will notice that the total cost line and the revenue line a quite close together. What does this tell you about the viability of the business? What sorts of things would have to happen to pull them further apart.? Tinker with your spreadsheet a bit to see how much impact changes in the parameters have on this situation.

Copyright © 2008 Gerald M. Myers
Last modified:09/05/2009 01:52:14 PM