tutorial in financial modeling_4_

Document Sample
tutorial in financial modeling_4_ Powered By Docstoc
					        Caribbean Internet Café: a tutorial in managerial accounting concepts and financial modeling using Excel

                         Caribbean Internet Café1 case:
    A tutorial in managerial accounting concepts and financial modeling using Excel

Assignment 1: Introduction

We will begin our work this semester with a case called Caribbean Internet Café
[hereafter, CIC]. In the CIC case, David Grant wants to open an Internet Café in
Kingston, Jamaica. This simple business setting is familiar; how many coffee shops,
drive-up or otherwise, can you think of that are within walking distance of campus?
Even if you are not a coffee-holic, the operation of one of these places should be fairly
self-evident. This assignment will help to fulfill a number of major objectives of the

      1. The CIC case makes use of a relatively familiar context in which to introduce
         many of the managerial accounting concepts we will deal with this semester. The
         familiarity of the context should enable you to associate managerial accounting
         terminology with real world phenomena.
      2. The structure of this series of exercises also provides an opportunity to develop
         your skills in using Excel to do financial modeling. Irrespective of your
         preferences for a career choice, financial modeling will be a critical skill in your
         professional life. Marketers and HR specialists take note: there is no avoiding the
      3. The majority of you are still relatively early in your path through the School of
         Business program. Case analyses will be an important element in several of your
         subsequent courses. One key to successful case analysis is meticulous
         organization of relevant facts and information. The approach I am suggesting is
         one way to ensure that you have all the information you need in one place.

We’ll work through the case over several days, developing an increasingly sophisticated
understanding of the concepts and terminology of managerial accounting. At times, the
CIC case will be very much in the foreground as we use David’s situation to illustrate
basic concepts. At other times, the case will be in the background, as we deal with
material from the text and/or other cases. Oh, by the way. I do realize that the case is
“old;” CIC’s Internet hookup is to be a 56 kbps leased line. However, that’s really
irrelevant for our purposes. Just assume they’ll have wireless and don’t worry about it.
And, oh, yeah, assume he's using Windows Vista, [or at least XP], not Win 95.

It is especially important that you follow the directions on this page as we work trough
the CIC case. Some parts of this series of assignments may seem like a lot of extra
work. However, I can assure you that a very methodical and structured approach to
analysis of a business problem pays big dividends in the long run. As you gain
experience, you will probably find you can take some shortcuts which will speed up your
 The Caribbean internet Café case is published by Ivey Management Services at the University of Western Ontario,
which holds the copyright to the case. This series of exercises to develop an Excel model was prepared by Professor
Gerald M. Myers, School of Business, Pacific Lutheran University. All rights not held by Ivey Management Services
are reserved to the author. This handout is intended for use in managerial accounting classes at PLU. For any other
use, please contact the author at myersgm@plu.edu. Not for reproduction or distribution.

b7d5d206-202c-4378-bc45-f12ca174121f.doc; 5/19/2012                                                                1
       Caribbean Internet Café: a tutorial in managerial accounting concepts and financial modeling using Excel

work. But, be careful. The best math teacher I have ever known cautions his students
“not to skip steps,” and I have learned through bitter experience that this is good advice
in problem solving of any kind, mathematical or otherwise.

Here is an overall roadmap of where we are going with this assignment. Our ultimate
goal is to develop a complete Excel model of David Grant's business. The model will
include several Excel worksheets, all in the same Excel workbook [or Excel file]:

                                                       What we'll use it for
                        All the data used elsewhere in the analysis. Everything in the other
                        worksheets will be cell referenced from the data worksheet.
          Brkeven       Calculate the contribution margin and breakeven point for the cafe
                        Develop mortgage amortization table for
                        David Grant's loan from JTL.
                        Develop balance sheets at various stages through the first two years
                        of the cafe's operation
         Incstmnt       Develop income statements for years 1 and 2 of the cafe's operation

        * To name or rename a worksheet, right click on the sheet tab in the active
        worksheet [probably Sheet1 if it is the first worksheet you have started in a given
        workbook]. Then click rename, and change the name to whatever you want it to
        be. Shorter names are better because the tabs are narrower and you can see
        more of them across the bottom of your screen. Also, avoid the use of spaces.
        Any space has to be filled with an underscore [ _ ] which gets to be a nuisance
        when you are trying to cell reference from one worksheet to another. So, the
        name bal_sht will work, but balsht takes less space and is easier to use.
        Obviously, you are free to use other names if you want to, but for consistency of
        reference, these are the names used in this illustration.

In principle, the entire case could be analyzed in a single, enormous worksheet.
However, the various sections of the analysis require different layouts, and using
separate worksheets facilitates the use of optimal column widths and row heights.

To cell reference from one worksheet to another, use the worksheet name, followed by
an exclamation point. So, if we want to reference cell A23 in the data worksheet to a
cell in the mtge worksheet, we would use the following notation: =data!A23.*

Constructing the data worksheet may take as much time as a good portion of the rest of
the analysis. However, careful attention here can pay off later. Cutting corners at the
beginning may make things harder later. In order to help you envision what this will look
like, I have provided a screen capture that illustrates the basic structure of my data
worksheet [see page 4]. You will want to refer to this periodically as you work through
the analysis. The step-by-step instructions which follow below are broken down into a

b7d5d206-202c-4378-bc45-f12ca174121f.doc; 5/19/2012                                                               2
       Caribbean Internet Café: a tutorial in managerial accounting concepts and financial modeling using Excel

series of numbered assignments. Refer to the assignment Web page for scheduling of
the respective assignment sections.

Assignment 2: Read the case

Read the case. Highlight [or underline or whatever you prefer] every financial or
economic fact given in the case which will have a bearing on the solution. As you
read the case, mentally separate the financial information into two basic types of
information: [1] those items that represent aggregate numbers [e.g., lump sums like the
manager's salary, rent, advertising, etc. as well as the items listed in Exhibit 1], and [2]
things that represent rates per hour, rates per customer, number of workers, hours
worked per week, and similar items.

Assignment 3: Set up your data worksheet

Set up an Excel worksheet with column headings like those in the screen capture on
page 4. As you can see from the screen capture, the first 35 or 40 rows will be devoted
to the "aggregate numbers" information discussed in the previous paragraph. I have
included several completed rows as examples.

Here is an explanation of the column headings. The role of columns A, B, and C should
be obvious. Column D [Category] will enable you to enter codes for different categories
of data. For the CIC case, you will need categories for assets, expenses, revenues,
startup costs, debt, capital and market information. I suggest that you code these as
A, E, R, SC, D, C and M, respectively, rather than using words. Codes are faster, save
space, are easier to use for filtering and searching, and not subject to spelling errors
which cause confusion later on. The purpose of column E [Cash?] is to separate the
items which will have an impact [either way] on cash from those that will not impact
cash. Columns F and G will enable you to easily go back to the case and verify the
accuracy of your data. Given the wealth of information that you may find in a typical
case [or in a real world situation] it is often helpful to keep track of where you found
things in the first place ["I know I saw that somewhere..."]. Column H will help you to
keep track of which items will have an impact on the financial statements and where
those impacts will occur. Column I is relevant only for the startup costs and the items in
Exhibit 1. General equipment, furniture, artwork and the flowers[!] are depreciated over
8 years. [I would think 8 year old artificial flowers would look a bit tacky, but...] The other
items in Exhibit 1 and the startup costs are amortized over 3 years. In the appropriate
cells in columns J or K, use cell referencing to calculate the annual write-off. For
example, the formula to get the 43,750 in cell J3 [see the screen capture], is =B3/I3.
Finally, note that column L is a second category column. We need to recognize that
depreciation and amortization are expenses. Where will these items show up? How?
Why? [Hint: in two places!].

b7d5d206-202c-4378-bc45-f12ca174121f.doc; 5/19/2012                                                               3
                             Caribbean Internet Café: a tutorial in managerial accounting concepts and financial modeling using Excel

b7d5d206-202c-4378-bc45-f12ca174121f.doc; 5/19/2012                                                                                     4
       Caribbean Internet Café: a tutorial in managerial accounting concepts and financial modeling using Excel

A couple of hints are in order here. Cases [and real world situations] often contain some
facts that are “interesting” but not anything we need to build into an analysis. For
example, we are told that JTL is 80% owned by a British telecommunications company,
and that JTL is one of 7 Internet service providers on the island. These bits of
information provide context and background, but since we are not doing a competitive
analysis of the ISP industry in Jamaica, we don’t need to make any particular note of
these facts. One of the skills we’re working on is learning to differentiate between critical
information and “filler.” Also, at the top of page 3 of the case there is information about
seating capacity of the cafe. That's interesting information, but we don't actually use that
data anywhere, so you don't need to include it in your worksheet. Finally, there is one
fact in the case that I cannot reconcile. On page 4 in the first sentence of the paragraph
under the "Pricing" boldfaced heading, the author refers to a 30% premium on the
average hourly Internet charge rates and arrives at a total of $120/hour. We know that
the average rate is $90 [page 2, first full paragraph, last sentence]; a 30% premium on
$90/hour would work out to $117 [90*1.3]. The case author apparently rounded the rate
to $120 for ease of calculation. However, we will change the premium to 133⅓%. In
your spreadsheet, use a cell formula to derive the $120 rate. This is important, since
the size of the markup over the $90 rate could be a factor in the competitiveness of the
cafe. For example, David might decide to reduce it to 110% of the average in order to
increase the volume of business.

As you read through the case, you will realize that David has to acquire a wide range of
items before he can open the door to the Café. It is important to classify these correctly
in setting up your worksheet, since that will affect the results later on. All of the items
listed in case Exhibit 1 will be classified as fixed assets. In general, fixed assets are
tangible resources which are relatively permanent; they are used to generate revenue.
You will find other items in the case which are classified as startup costs. These will be
shown on the balance sheet as intangible assets. Startup costs are investments in
economic resources which enable a business to get started, but they are not operating
costs [that is, routine, day-to-day expenses]. These are classified as intangible assets
on the balance sheet. David Grant will write these off [amortize is the technical term]
over 3 years. David’s startup costs include up-front marketing, his license fees, and
utility deposts. Note that “startup costs” and investment in fixed assets are not the
same. The cash outlay for fixed assets is obviously necessary to get the business
started, but we reserve the tram startup costs for those specific items

After you have listed all the aggregate items, insert a sub heading [something like
"Other information" and then list the individual rates and quantities, along with
appropriate labels [e.g. "workers per shift" or "average customer check for drinks"]. Fill
in the page and paragraph numbers where you find these bits of data. You don’t need
the code column for these items.

This worksheet will be the source of the data we'll use for much of the rest of the
assignment. Right click on the sheet tab in your worksheet [probably Sheet1], click
rename, and change the name to Data. .After you have gone through the entire case
and recorded all the facts in your table, save your file. Actually, it’s a good idea to save

b7d5d206-202c-4378-bc45-f12ca174121f.doc; 5/19/2012                                                               5
       Caribbean Internet Café: a tutorial in managerial accounting concepts and financial modeling using Excel

your file periodically anyway, and if you have not set the autorecover on your computer,
do so NOW. In Excel [or Word], click Tools, Options, Save, check the autorecover box
and then set the time for something like 10 minutes. We will be working on this model
throughout the semester. Keep an updated copy of your Excel file on your hard disk and
another on a pen drive or memory stick. Bring your file to class regularly, since we may
be using the model on the spur of the moment to illustrate concepts and relationships.

After you have finished the data worksheet, print it, including the row and column
headings [A, B, C... across the top, 1, 2, 3...down the left side]. This will enable you to
see what cells you need to reference in developing the rest of your model. [To get the
row and column headings, specify Print preview, Setup, Sheet, and then check the
"Row and Column headings" box.]

Assignment 4: Balance sheets when David Grant starts the business

To structure the specific tasks which follow, we need to establish some dates. We know
that the time of the case is June [first sentence, page 1]. Ignoring the possibility for
delays in equipment installation, haggling with JTL over the terms of the agreement and
other realistic problems, let's assume the following dates.

        June 28th: David formally establishes the business [he hasn't bought anything yet and
        has no agreement with JTL, but has withdrawn the $500K in savings from his personal
        bank account and invested it in his business.
        June 29th: David reaches agreement with JTL [there are signatures, a handshake,
        pieces of paper are exchanged and David makes a celebratory trip to the bank]
        June 30th: David buys all the equipment he needs, has it installed, hires the staff,
        makes all necessary arrangements for insurance, Internet hookup, etc. and will be ready
        to open for business promptly at 6:00 am on July 1. Presto! Grand opening, fireworks,
        live music, whatever.

Open a new worksheet in the same Excel workbook as your Data worksheet. Rename
this new worksheet Balsht [balance sheet]. In this worksheet, set up three balance
sheets: one for the end of the day on June 28th, a second at the end of the day on June
29th, and the third at the end of the day on June 30th. Assume that all startup costs are
paid for in cash and that all equipment, fixtures, software etc. [the list in case Exhibit 1]
are paid for in cash as well. For our purposes, it probably works best to use a "side by
side" format for the balance sheet [assets on the left, liabilities and equity on the right.]
Put the labels in columns A and C and the amounts in columns B and D, Do not skip
columns in between. Use the first few rows for the June 28th balance sheet, the next
few for the June 29th balance sheet and so on. Before completing the balance sheets
for June 29th and 30th, you will need to calculate the cash balance at the end of the
day. Use cell referencing and a clearly labeled set of calculations off to the side to
derive the cash balances. You want to be able to SEE the details that are necessary to
arrive at the respective balances. Obviously, you could calculate the whole business in
a single cell, but you wouldn’t be able to see the individual pieces of the calculation,
thus making any error much more difficult to find.

b7d5d206-202c-4378-bc45-f12ca174121f.doc; 5/19/2012                                                               6
       Caribbean Internet Café: a tutorial in managerial accounting concepts and financial modeling using Excel

To calculate the June 30th balance sheet, you will need the total amounts of investment
in fixed assets [fixtures, equipment, etc.] and startup costs. There are a variety of ways
that we could do this, but a good tool to use at this point is the =DSUM function. The
DSUM function is of the general form =DSUM(database,field,criteria). The arguments
of the function are as follows: database is the range of cells [column headings and rows
of data] from which information is to be extracted. Each column heading [a field name in
database-speak] must be unique. Field is the focus of interest; which column do we
want summary information about? Criteria is a range of cells which includes an exact
replication of the first row of the database [the field names] and one additional row. In
the blank row below the field names. we specify the criteria for identifying the rows
[records in database-speak] of interest. The portion of my worksheet where I extracted
the information on the total investment in fixtures, equipment, etc., looks like this:

Note the formula in cell B81. The database is in A1:M27. The information we want to
extract is in column B [Amount] of the database; thus the B75 cell reference as the
second argument of the function. Finally, the criteria range is A75:M76. The "A" in cell
D76 tells Excel that we want to filter out everything except the items coded "A." [Note
that we could have truncated the database to A1:D27 and the criteria range to A75:D76
because everything to the right of column D is irrelevant in this specific situation. If we
want to extract information about 8-year depreciation, we would need to include
columns A through J. [Database columns must be contiguous. Also, you should be
aware of the fact that the specification of the "A" in D76 is not case sensitive in Excel.
Note also that we could use a compound filter. Insertion of an "8" in cell I76 would give
us the total investment in 8-year assets only.]

Establish additional criteria ranges and use separate DSUM functions to determine the
total investment in startup costs, the total annual depreciation for 8-year fixed assets,
the total annual depreciation for 3-year fixed assets, and the total annual amortization
for startup costs.

Assignment 5: Mortgage amortization table

b7d5d206-202c-4378-bc45-f12ca174121f.doc; 5/19/2012                                                               7
       Caribbean Internet Café: a tutorial in managerial accounting concepts and financial modeling using Excel

Create a new worksheet in your workbook. Rename it mtge [for mortgage]. We'll
determine the annual and monthly loan payments that David Grant will have to make.
Set up cells as follows:

Row/Column                              A                           B                  C
         1                   Amt borrowed
         2                   Interest rate
         3                   Years
         4                   Periodic payment
         5                   Annual payment
         6                   Total interest

In cells B1, B2, and B3 enter the references to the cells in your data worksheet for
respective items listed in column A. In cell C2, enter =B2/12 to find the monthly interest
rate. In cell C3, enter =B3*12 to find the total months for the loan. In cell B4 we'll use the
=PMT function to find the periodic payment. The =PMT function has the arguments
=PMT(rate,nper,PV), where rate is the interest rate, nper is the number of periods, and
PV is the present value of the debt. It is absolutely essential to be consistent with the
interest rate and the number of periods. If you use an annual interest rate, you must use
the number of years the loan will be outstanding. If you want monthly payments, you
must use the monthly interest rate and the total number of months that the loan will be
outstanding. Note that you cannot simply calculate the annual payment and divide it by
12 to get the monthly payment. [Why?] Finally, note that by default, the function returns
a negative amount for the payment. This is an arbitrary decision that Microsoft made in
writing the code. I prefer to make my own decisions about signs given the context of the
analysis, so I always enter the amount borrowed as a negative amount in the =PMT
function. The payment amount returned by the function is then positive and I can deal
with it however I choose in subsequent formulas. Do what you want to do with this, but
watch out because it's easy to get messed up if you neglect to compensate for the
negative sign when you should have done so.

Now develop an amortization table for the life of the loan. A screen capture of my
mortgage amortization table is on the next page. Enter cell formulas in columns C, D,
and E to calculate the payment, interest, and principle. The payment cell is simply the
monthly payment that you have already found. The balance due column is a
progressively declining amount as the principle gets paid off. When you get to the end
of month 60, the balance due should be zero. After you have finished the amortization
table, use the DSUM function to extract the total interest and principle payment
[separate amounts] in year 1 and again for year 2.

b7d5d206-202c-4378-bc45-f12ca174121f.doc; 5/19/2012                                                               8
       Caribbean Internet Café: a tutorial in managerial accounting concepts and financial modeling using Excel

Assignment 6: Calculate total fixed costs

Before we can find the total fixed costs for the business, we need to calculate the cost
of the student wages. Realistically, these are probably a variable cost [one that is
constant per unit, but changes in total in direct proportion to sales]. However, treating
the student wages as variable in this situation makes the subsequent calculations more
complex than necessary, so we'll assume that they are a fixed cost. In your data
worksheet, insert a blank row directly under the row for the manager's salary; [for
convenience, we'll keep all the compensation costs in adjacent rows]. Enter "Student
wages" in column A. In column B, enter a cell formula to multiply the wage rate times
the hours per month times the student per shift times the weeks per month. Assume all
months have 4 weeks.

Now we are in a position to calculate the total fixed costs. Remember that fixed costs
are those that remain constant in total irrespective of the volume of business we do
[within the relevant range]. All the monthly costs here are fixed except the cost of food
and beverages, which we'll deal with later. At the bottom of your data worksheet, use
the =DSUM function to extract the total fixed costs per month. You will need to do this in
two steps. Step one will extract the monthly payments for salaries, rent, utilities, etc.
Step two will extract the depreciation and amortization. In order to get the total fixed
costs, however, we need one additional item: interest. A screen capture of my
worksheet to extract the depreciation and amortization looks like this:

b7d5d206-202c-4378-bc45-f12ca174121f.doc; 5/19/2012                                                               9
       Caribbean Internet Café: a tutorial in managerial accounting concepts and financial modeling using Excel

The section to capture the cash fixed costs looks like this:

The value in cell B122 in the screen capture above is a check figure for the total fixed
costs for year 1. Note that the amounts you extracted from column B with the =DSUM
are per month. You will need to multiply the =DSUM function by 12 to get the annual

Assignment 7: Derive variable costs, contribution margin, and breakeven points;
calculate net income

b7d5d206-202c-4378-bc45-f12ca174121f.doc; 5/19/2012                                                               10
       Caribbean Internet Café: a tutorial in managerial accounting concepts and financial modeling using Excel

Open a new worksheet and rename it brkeven [for breakeven]; we'll use it for
calculating the contribution margins and the breakeven point. Here is what my
worksheet looks like.

Cells B3:B8 contain the cell references or formulas to calculate the revenues David will
get from the sale of beverages and food and from customer use of the Internet. Cells
C3:C8 contain the cell references or formulas to calculate David's cost of supplying
those goods and services. All of the amounts in rows 4 through 8 are per unit. Row 9 is
simply the formulas to find the contribution margin ratio. Yes, the $144 in D8 is a check
figure for the contribution margin per customer. Note that it is necessary to weight the
Internet charges and costs for the proportion of customers who will use the 'Net. I used
row 10 to convert the amounts in row 8 to US currency and then did the same for the
breakeven in dollars. The discount in converting Jamaican to US currency is so large
that it makes any mental comparison of costs and prices difficult. It's hard to visualize
paying $200 for a double mocha and a scone. Try the exchange rate given in the case
and then go to www.exchangerate.com and get the current rate, which is even more
favorable to US visitors than it was at the time of the case.

In columns G, H, and I, create projected income statements for a year, a month
[assume a month is 1/12th of a year], and a day [assuming a 24 day-a-month operation].
Note the check figure in cell G10. As noted, these income numbers are for the
“average” or “realistic” levels of operation.

Assignment 8: Test your model

In your data worksheet, change the target population to 21,000. If you have cell
referenced everything correctly, your new realistic annual net income should be

b7d5d206-202c-4378-bc45-f12ca174121f.doc; 5/19/2012                                                               11
       Caribbean Internet Café: a tutorial in managerial accounting concepts and financial modeling using Excel

Now test your understanding of what's going on here. Go back to your data worksheet
and change the target population to 20,001. That’s right. We’re going to find the
incremental impact of one additional potential customer. What is the incremental
increase in annual net income under the realistic conditions? With a pencil, a pocket
calculator, and a few very simple calculations, you should be able to arrive at this
number and prove that your spreadsheet analysis is correct.

Assignment 9: Marketing strategies for the Caribbean Internet Café

Suggestion: to make analysis of these alternative scenarios easier, create a cell
reference in a convenient location in your data worksheet to the annual net income cell
in your brkeven worksheet from assignment 6. This will enable you to easily see the
impact of a given change on net income without toggling back and forth from one
worksheet to another. In each case, you should be able to prove your results with
pencil, paper, and pocket calculator. Finally each of these scenarios is independent;
be sure to change all data cells back to the original numbers before proceeding to
the next scenario.

    A. Refer to the original data. Grant assumed that 40% of his customers would use
       the internet. Suppose that, in an effort to stimulate use of the ‘Net by his
       customer, Grant cuts the markup on the average hourly Internet access charge
       to 110% [rather than the 133% originally specified]. Assume further that after a
       survey of customers, Grant figures that the decrease in the charge for the
       Internet will raise demand so that 55% of his customers use the ‘Net. Use your
       spreadsheet to determine whether the increase in Internet usage is sufficient of
       offset the revenue lost due to the reduction in the charge for Internet usage. If the
       increase in usage is not sufficient to offset the lost revenue, to what level [i.e.,
       percentage of customers] would Internet usage have to increase for Grant to be
       indifferent between the original assumptions and the revised estimates.
    B. Let’s look at advertising. Assume that Grant decides that an improved advertising
       program would enhance business. The new ads would make greater use of
       broadcast media, but would double monthly marketing costs to $20,000 from the
       current $10,000. The increased advertising would raise the proportion of
       customers in the target population [under the realistic assumptions] from the
       current 40% to 45%. Should the advertising program be undertaken?
    C. David wonders whether he might do better if he bought pastries of a slightly
       higher quality. A local baker has offered Grant a deal which the baker believes
       would enhance the sales of both businesses. The baker would be able to provide
       the required quantities, and delivery schedules are not expected to be a problem.
       The average cost of food per customer would rise from the current $30 to $36.
       Because of increased demand for the higher quality goodies, the average
       customer check for food would increase to $67. However, Grant’s agreement
       with the bakery would require that he devote an additional $2,000 to a
       cooperative marketing program that promoted the two businesses. Should Grant
       go along with the baker’s offer? Why or why not?

b7d5d206-202c-4378-bc45-f12ca174121f.doc; 5/19/2012                                                               12
       Caribbean Internet Café: a tutorial in managerial accounting concepts and financial modeling using Excel

Assignment 10: Develop income a CVP graph for the realistic assumptions.

You will need to create a table with columns for volume, total revenue, total variable
costs, and total fixed costs. The volume column should be in customers per day, from 0
to 120, in 5 unit increments.

Assignment 11: Develop income statements for years 1 and 2 under all three
potential scenarios [optimistic, realistic and pessimistic].

We have already set up an income statement for the realistic conditions. However, it
would be convenient to have income statements under the three scenarios in parallel
columns so that we can more clearly see the ramifications of the level of business. My
incstmnt worksheet looks like the screen capture below. [In the rows directly below this
section, I set up a comparable table with the entire grid converted to US currency so I
could get a better sense of what the numbers were.] Note the check figure in cell C12.

Assignment 12: Create end of year balance sheets

Create the balance sheet for the end of year 1. You should be able to do this relatively
quickly, once you have determined the cash balances for each assumed level of sales.
You will need three balance sheets, one for each anticipated level of business
[optimistic, realistic and pessimistic], and three sets of cash calculations. Check figure:
the balance sheet total for the realistic setting is $2,630,663.

Assignment 13: Performance measurement

David [and JTL] would like some assessment of the Café’s projected performance.
True, we have net income figures and balance sheets for years 1 and 2, but some
additional performance indices would be useful to David in managing the business and
to JTL in evaluating its investment in this enterprise.

b7d5d206-202c-4378-bc45-f12ca174121f.doc; 5/19/2012                                                               13
       Caribbean Internet Café: a tutorial in managerial accounting concepts and financial modeling using Excel

Create a new worksheet [perf, for performance]. Use cell referencing to create a table
of performance measures for years 1 and 2 under the realistic and pessimistic
conditions. The first two rows of your table should probably look something like this:

                                                 Year 1                                    Year 2
Performance measures                     Realistic     Pessimistic               Realistic      Pessimistic

Calculate the following performance measures: return on assets, return on investment,
return on sales, debt to total assets, and the debt to equity ratio.

b7d5d206-202c-4378-bc45-f12ca174121f.doc; 5/19/2012                                                               14

Shared By: