Home

  [Home]

Be sure to read this entire page and refer to it often as you work on the College Couriers Case. Close attention to the instructions and modifications in assumptions included below will save you much grief, hair tearing, heartburn, etc.

Final project presentations: The final project in this class requires the development of a financing proposal for a small business, based on a published case. This semester's case is College Couriers [Ivey Publishing, in the packet from the bookstore]. Read the following instructions and notes carefully. Close attention to the information given here will save you much pain in completing the analysis of this case.

This is a group project. Groups will consist of 2 or 3 [NOT 4] people. Groups should be assembled by November 1. You are free to make up your own groups. I will not serve as a matchmaker. Each group will meet to agree upon their own "rules of engagement," which are to be submitted to me in writing on November 10th. Each member of the group must sign the rules of engagement, which I will retain. In the event of a group "blow-up," your rules of engagement will be the framework for a decision about what to do. Your "rules" should address such things as [but not restricted to] sharing of effort, attendance at meetings and procedures for arranging meetings. The rules of engagement are not graded--indeed, I will not even read them--UNLESS someone in your group comes to me and complains that so-and-so is not doing his/her share of the work, not showing up for meetings, or whatever. I will then produce YOUR rules of engagement and ask you what you propose to do to resolve the situation. If your team is experiencing problems, PLEASE report the situation to me BEFORE you get to the "breaking point."  I cannot force you to cooperate if you don't want to, but I do insist that major problems be reported to me immediately simply to ensure that I know what is going on.  As a last resort, groups may FIRE recalcitrant members who refuse to cooperate according to the rules you have established; such action REQUIRES a meeting with me first. At this meeting, we will determine whether reconciliation and continued work as a group is possible. If not, I will toss a coin to determine who gets to continue with the original case. The loser of the toss will have to start from scratch on a new case, which is virtually guaranteed to be more difficult than the original. Consequently, there are significant incentives for cooperation and learning to work together. Trust me: You do not want to go down this road; pick your partners carefully and learn to work with one another. [Welcome to the real world. We seldom get to choose our professional colleagues, and we may well wind up working with folks who are irritating or downright nasty.]

Each team will meet with me and Craig Chance, a PLU MBA alum and a commercial banker with Columbia Bank. We will "interview" you as applicants for the financing which the Dwayne Leslie will need to finance his acquisition of CCI. All meetings will be held during the final exam period. A schedule for specific group meeting times will be announced after groups are formed. We strongly recommend that you NOT bring note cards or other "crutches" to the interview. Suppose this was a real situation and you were the banker, interviewing someone seeking funding for a small business. How would you respond if the applicant was so unsure of his proposal that he had to have notes to remind him about what he wanted to say? Would you have confidence in his ability to manage the business?

College Couriers: Notes, Hints, and Other Stuff 

I know you have all had more than enough of the Exotic Imports case. However, before using the Excel model as the basis for another situation, [like College Couriers] you should test it to be sure that it works under a variety of different assumptions. Any hidden problems, if left as is, could erupt in nasty ways later on. Trust me—you don’t want to find out the hard way that there are problems which could have been easily solved. Although I have looked at everyone’s file, modeling errors can be subtle enough so that even the practiced eye doesn’t catch until something “blows up.” Therefore, BEFORE you start College Couriers, I encourage you to do three tests on your Exotic Imports model. If you encounter difficulties, I will be glad to help you try to find the error[s].

Step 1: If you have not done so already, add cell references in F1:F3 of your data worksheet [with appropriate labels in E1:E3] so that you can easily see the ending totals for the respective sides of the September 30^th balance sheet and the net income for the 6 months. [See the Acrobat printout for reference.]

Step 2: In cell E4 of the data worksheet add the label Maximum line of credit and in cell F4 of the data worksheet, enter the formula to calculate the maximum value of the line of credit. The formula would be: =MAX('Cash budget'!B15:G15) if your cell coordinates are the same as those in the Acrobat printout that you have.

Cells F1:F4 are your test cells. They represent specific critical outcomes which will change as the parameters of the model change. They would obviously be of particular interest to a manager evaluating various scenarios, but you can also use them to determine whether the model you have developed gives consistent results. So…do the following tests; if everything works, this won’t take more than about 10 - 15 minutes. If it doesn’t, you would rather know now than later. Note that the tests are to evaluate the robustness of the model; they are not designed to represent “reasonable conditions” or “likely scenarios.”

Test 1: Change your initial [April 1^st] balance sheet values as follows: cash = 0 and equity = 70,000. The original balance sheet will still balance. Your “test cells” will now look the second panel of the table below. [The original results are provided for reference in Panel 1 of the table.]

Test 2: Now change the beginning cash to 930,000 and change the equity to 1,000,000. Once again, the initial balance sheet will balance. The test cells now look like panel 3 in the table.

Test 3: Now change the beginning cash and equity back to their original values [30,000 and 100,000 respectively]. Check to be sure the test cells are correct at their original values. Then change the sales for April [B10 in your data worksheet if you followed the arrangement in the handout] to 0 and the sales for May [cell B11] to 600,000. Your test cells should now look like panel 4 of the table below.

Now...on to College Couriers:

The section that follows below contains a number of simplifying assumptions and explanatory notes; these are designed to provide some guidance for the case. Careful attention will spare you many hours of agony. Read carefully, and remember the rule: METICULOUS ORGANIZATION OF LARGE AMOUNTS OF INFORMATION. 

1. This whole assignment will be easier if you start with your Excel file from Exotic Imports. Open your Exotic Imports file, do a "file save as...," changing the name appropriately. You will need to add and change labels, add and/or delete rows and columns, etc. However, you have the basic structure, especially for the calculations of the line of credit. This is one reason  why you need to have finished Exotic Imports [to which you know the answers] before starting work on College Couriers [to which you do not know the answers].
2. Assume that Leslie is seeking a line of credit from the bank. A line of credit is different from a mortgage or loan. A mortgage is for a specific sum of money [the principle], which is borrowed for an agreed-upon period at a stated interest rate. The principle is paid back according to a predetermined schedule. Payments are the same each month, and include principle and interest. Early payments are mostly interest because the principle is relatively large, but the proportions of principle and interest reverse as the value of the outstanding debt declines. Under a line of credit, the borrower has authorization to borrow up to a certain maximum amount. Money is borrowed as needed, and repaid as cash flows permit. Interest accrues continually on any outstanding balance and is typically paid monthly. We will assume that any borrowing Leslie requires will occur at the beginning of the month and all repayments occur at the end of the month. Borrowing and repayment NEVER occur in the same month. Interest on the line of credit accrues monthly and is paid in the next month.
3. Note that there is also a loan payment to Thomas [$1,500/month, with interest on the unpaid balance due on 12/31]. Note also that payments to Thomas begin in February. Do not confuse this with the line of credit from the bank. Note that there will be TWO different interest payments: [1] interest paid to Thomas as specified on page 9 of the case; and [2] interest paid to the bank on the line of credit. Assume that interest on the line of credit is accrued monthly, and paid in the first month after accrual. [January's interest is paid in February, etc.] Assume that Leslie has to maintain a minimum cash balance of $3,000.
4. On page 6, it says that "Thomas billed his clients on the last day of the month...and expected payment within 30 days." Your analysis of accounts receivable should be sufficiently flexible to accommodate payment schedules ranging from "100% of sales are paid in the month of sale" to "all sales are on account, with payment received through the second month following the sale [e.g., some receipts for January sales might be received in March]. You should also include the potential for bad debts. Do not create separate worksheets; just use appropriate cell references to link the payment and bad debt percentages to the appropriate months.  Assume a potential worst case scenario that is at least as bad as the following pattern: all sales on account, with 75% of the total collected in the month following the sale, 20% collected in the second month, and 5% bad debts.
5. Note that this business is unusual when compared to those we have studied over the course of the semester. They don't make anything [no canoes or valve stems] and Leslie is not buying monetary assets [cash, receivables] or physical assets [inventory, a building]. He is buying an intangible asset--Thomas's client list--and the right to use it in his own business. Assume that the value of the client list is amortized over 3 years. Note, however, that Leslie is paying Thomas over 2 years.
6. At the bottom of page 6, the case says that Thomas paid $150/quarter to an accountant and paid a friend $50/month to process bills. On page 8, it says that Thomas could buy a computer and printer to do billing for $1,000. These are not either/or scenarios; assume that Leslie will use the services of the accountant [for the same fee] AND buy the computer and printer. Assume a 5-year life for the computer and printer. Assume that Leslie will NOT need the services of the "friend." Note that the accountant is paid in the first month of every quarter.
7. Assume that the phone and the name change have a five year life.
8. Assume that Leslie uses 12 tanks of gas per month.
9. Note that the case opens in December 2003. To simplify the analysis, assume that Leslie purchases the business at the stroke of midnight on December 31, 2003, and is ready to operate bright and early on January 1, 2004. On page 7, it says that Leslie has $3,500 that he is ready to invest in the business. What does the $3,500 represent? What will the balance sheet look like immediately after Leslie invests his life's savings? [Don't worry about whether the bank is open at midnight and that sort of thing. Assume that any tasks Leslie has to do prior to opening [buying the computer, setting it up, running to the bank, etc.] can be done instantaneously and that there is no delay in starting operations.
10. Prepare a balance sheet as of the start of business, assuming that all transactions necessary to begin operations have been recorded. He has bought the computer, printer and phone, paid for the pre-opening promotion, and sealed the deal with Leslie. Note that at this point there will be no balance on the line of credit.
11. Your cash budget will need to include ALL transactions affecting cash: his investment in the business, acquisition of the computer and printer, payment for startup costs, as well as routine operating expenses and debt service.
12. Notice that Leslie is paying Thomas $1,500 per month, starting in February, and that interest is due on the unpaid balance on December 31. This is simple [not compound] interest, and is just a flat 3% on the amount of principle outstanding. A simple amortization table will facilitate calculation of the progressively-declining balance in the loan from Thomas.
13. A not so gentle hint: DO NOT bury multiple things in the same cell. My first attempt at the end of year balance sheet [December 2004] was off by $188. I realized that I had  initially combined the depreciation on the fixed assets and the amortization of the client list in the same cell. After unsuccessfully trying to find the error elsewhere, I finally broke that down into two separate cells. Bingo! Error found!

Project deliverables [organization-speak for the materials you have to produce]:

You are to prepare a detailed master budget for College Couriers, including a sales budget, cash receipts budget, disbursements budget, cash budget, income statements, and balance sheets. All quantitative work MUST be done on Excel. All schedules are to be monthly, except the balance sheets. You will need beginning balance sheet [see #9 and #10, above] and a year-end balance sheet; do not create monthly balance sheets.

For the presentation itself, please do not bring Power Points. Bring a hard copy for each person in the room [one for each group member + 2] and email me your file no later than an hour before your scheduled final exam session i.e., 7:00 am on 12/16 for section 01 and noon on 12/17 for section 02. Use the last name of one group member and the word "courier" in the filename [e.g., smithcourier.xls]. Your email subject line MUST contain the phrase college courier. Please do not use other phrases or descriptive words. Failure to follow these procedures will result in a failing grade on the exam for all group members.

Requirements:

The minimum requirements for the final project include:

1. an executive summary supporting your application for the credit line (do NOT include basic facts except as necessary to explain your analysis).
2. a full, accurate master budget with all supporting schedules, including the items described above. You should include a discussion of breakeven points, margins of safety, and related matters.

These are minimum requirements. Simple completion of 1 and 2, above, cannot get you an A on the project; if you do all of these, and do them extremely well, you would probably get a B, subject to the qualifications below.

Weak performance on the above items will result in a commensurately lower grade. In evaluating your work I look particularly for an understanding of the conceptual material related to the assignment. Incorrect cell references and clerical errors are a problem, but they are typically easily corrected. However, evidence of a lack of understanding of basic concepts [e.g., contribution margins, what goes on the balance sheet and where, cash vs. accrual, having the content and organization of the income statement correct] is far more serious. Equally serious is the inability to interpret or use numerical results, when the results themselves are correct. The presence of one or more conceptual errors will differentiate between B papers and those which descend to the C [or lower] category. One or two major conceptual errors can have an enormous impact on your grade, so your ability to demonstrate a thorough understanding of the concepts is critical. Remember that quantitative and graphical exhibits do not stand for themselves. They require interpretation and understanding. You cannot assume that the bankers will see what you want them to see.

By this time, you should have developed enough expertise in financial modeling using Excel so that evaluation of alternative scenarios is facilitated by thoughtful workbook organization, use of Excel functions, and other techniques. Your calculation of the line of credit balance and associated interest MUST use the =IF( ) modeling that we have used in class. Failure to use this capability will result in a failing grade on the project.

Another differentiating factor will be credit awarded for things like the items below, based on thoroughness, creativity, and quantitative accuracy. Bear in mind that all analyses must be reasonable; for example, it would be highly unrealistic to assume that the revenues could be double the amount given in the case for the existing owner.

Discussion of such issues must be supported by appropriate quantitative analysis. It must be reasonable and based on an understanding of the industry and of relevant market phenomena. Professional presentation is expected. Poorly written, badly organized, sloppy work will receive no credit.

Copyright © 2010 Gerald M. Myers
Last modified: 1/31/2011; 17:24