|
The Caribbean Internet Cafe [hereafter, CIC] case is a short case that
illustrates many of the concepts that we will deal with this semester. It
provides an excellent opportunity to explore the transition from financial
accounting [BUSA 202] to managerial accounting. We will deal with assets,
liabilities, revenues, expenses, investments, cash flow, and profitability. In
the context of the CIC case, we will learn about fixed and variable costs,
contribution margin, breakeven analysis, as well as the development of projected
financial statements based on alternative scenarios.
Assignment I [September 10th]; there is nothing to hand in, but complete the
following:
- Read
the Caribbean Internet Café case
- Assume that you are David Grant. Write down at least three
questions that you would ask BEFORE committing yourself to can investment in
a business proposition like CIC. Now assume that you are a representative of
Jamaica Telecommunications, Ltd. Write down three additional questions that
you would ask BEFORE agreeing to invest in this fledgling business. How do
the interests of David Grant and JTL converge? How are they different?
- Identify every monetary item in the case and classify it as an
investment, a continuing expense, revenue, an asset, or a liability.
- Open a
new Excel workbook and rename one of the worksheets as data.
ALL the raw data from the case will go in this worksheet. When
we start doing summaries and calculations, we'll use cell referencing to
create a number of separate worksheets for various parts of the analysis.
- Set up a
matrix in Excel like the example shown here:
You will have to adjust the number
of rows; the spacing in the illustration is not indicative of the number of rows you will
need.
Here are some notes about what goes in each column:
-
Column A: Use of the “Description”
categories in column A [boldface] will ensure consistency between in your work and
those of others in the class.
-
Column B: "Amount or value" is
exactly that—how much money is involved?
-
Column C: "Period or units"—is this
a lump sum [one time occurrence], or is this a monthly
or annual amount? Some of the items in the "Other Information"
section [line 24] are per hour or per week. Others are simply units of
measure [e.g., the number of people in the target population. Specification
of these information can be extremely helpful later on.
-
Column D: "Category"--is it an
asset, liability, expense, revenue, or investment? Use A, L, E, R, or I,
respectively.
-
Columns E and F: On what page
[or in what exhibit] and in what paragraphs in the case
did you find it? This is extremely useful when you have to go back and verify
something. The CIC case is “only” 5 pages; with a longer case, you can waste a tremendous
amount of time trying to locate facts that you “know are there
somewhere.” [Trust me. Been there. Done that.]
-
Column G: for assets, what is the
life [number of years] specified? This simplifies calculation of depreciation.
-
Columns H and I: calculated
depreciation for 8-year and 3-year assets, respectively; putting the
depreciation in separate columns facilitates keeping track of the two asset
categories; divide column B by column C, using cell referencing.
You can just total the respective columns to arrive at the total annual
depreciation write off for each asset group.
Assignment 2 [September 15th]; noting to hand in...yet.
-
Summarize the COSTS in the CIC case; which ones are fixed and which ones
are variable? Create a new worksheet in your CIC Excel workbook; make a
list of the costs, listing the fixed and variable in separate columns.
Determine the total for each category. Use cell referencing.
-
Create a new worksheet; rename it mtge. Set up a
mortgage amortization table, using the illustration at the right as a guide.
 The
mortgage amortization period specified in the case is 5 years, so you will
need to extend the table beyond the two years shown here. The periodic
payment in cell D5 is found using the Excel =PMT function; the arguments for
the function are the interest rate, the number of periods, and the present
value of the debt. Because we are assuming monthly payments, we need a
monthly interest rate, which is calculated in cell E3. Note that the shaded
rows [every 12th row] are not totals; I have highlighted them simply to mark
off the years. We'll extract the totals for the respective years using the
Excel database functions. When you get down the the 60th month, the amount
in the "balance due" column should be essentially zero [mine
actually comes out to 0.000000007261...]. EVERYTHING should be
cell-referenced so that if you decide to change something in your DATA
worksheet [e.g., he borrows $1.5M, or the interest rate goes to 12%], the
entire mortgage table will recalculate.
-
Now we will use the Excel DSUM function to calculate the total principle and
interest payments in year 1. The database is the entire range from which we
want to extract information: A9:E69 if you set it up the way I did. Note
that the database includes the header row. The header row can be only a
single row, and each column [field] in the database must have a different
heading. The criteria range consists of the same headers [copy them to
ensure that they are identical] and one additional row where
the criteria for the data we want to extract are specified. We use the DSUM
formula in the output row [row 74, below] to extract the specific data we
want. The arguments for the DSUM function in E74 are as follows: $A$9:$E$69
is the data input range [the database]; D9 is the field [the interest] we
want to sum; $A$72:$E$73 is the criterion range [the row of copied headers
and the specification of how we want the data to be extracted [for year 1].
To get a feel for how this works, erase the 1 in cell A73 and put a 4 in
cell B73; Excel will extract the sum of all the payments for April [April
year 1 + April year 2....].
- To extract the principle and interest for year 2, you could just change
the value in A73, but then we would lose the information about year 1.
Eventually we will want to summarize the cafe's performance for both years 1
and 2 at the same time, so set up another worksheet range to extract the
principle and interest payments for year 2.
- Set up balance sheets. The case is set in June 1996. For simplicity,
let's assume that Grant opens the shop bright and early on July 1. We'll
assume that Grant was really busy at the end of June and did all the things
necessary to open the cafe within a couple of days.
- June 29th: Grant invests his own $500,000, gets the $500,000 equity
stake from JTL as well as the $1,250,000 loan from JTL [we said
he was busy] and takes the whole business to the bank and sets up and
account in the name of the business. Prepare a balance sheet as of the
close of business on June 29th.
- June 30th: Grant buys all the equipment, hauls everything to the
site of the cafe, sets everything up, hires the manager and the student
workers, and gets the utilities hooked up. By about the evening of the
30th, Grant is pretty tired, but he is ready to open the next morning.
assume that the first thing he will do tomorrow morning is write a check
for the July rent. Prepare a balance sheet as of the close of business
on June 30th. Assume that Grant paid cash for all the items that he had
to buy prior to opening.
- Now let's see what David's breakeven point looks like. Create a new
worksheet [brkeven] and set up the following table:
The 40% in B1
is cell referenced from the data worksheet; we want ot test alternative
scenarios to see what happens if more or fewer people access the Internet.
Develop cell formulas to calculate the revenues, variable costs, and other
values shown above. Note that the exchange rate for the Jamaican dollar against
the US dollar [35:1] is given in the footnote on page 2. If you have not done so
already, add a cell to your data worksheet for the exchange rate; you will need
to find the conversion factor, which is the reciprocal of the given exchange
rate. It's
always a good idea to convert currencies to something you are familiar with, so
that's the reason for the "In US$ section in D13:E15. [For the record, the
current exchange rate is about 90:1.]
|
