|
The Sombrero Fruit Juice Outlet [hereafter, SFJO] 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--all
concepts you covered in BUSA 202. Important managerial accounting concepts used
in the SFJO case include fixed and variable costs,
contribution margin, and breakeven analysis; we will also develop projected
financial statements. Note that the proposed venture is in the Abu
Dhabi Emirate, and that the currency amounts are in dirham [DH].
Objectives: The objectives of this entire series of assignments are as
follows:
- understand the motivations and interests of the parties involved [i.e.,
Moawia and the bank that is going to lend him the DH 20,000]
- understand and identify the financial information provided in the case
- use your understanding of managerial accounting concepts to analyze the economics of the business
in a familiar, simple setting
- develop basic skills in financial modeling using Excel spreadsheet
software
Close attention to the instructions below will save you considerable time and
grief, ensure that you understand what we are doing, and will enable you to
learn as much as possible from this series of assignments.
Assignment #1:
- Read the case. If you have not read chapter 1 in the text, read that
first. THEN read the case.
- What is Moawia's apparent equity stake in the business? You you
think this is realistic? Why or why not?
- Assume that you are Moawia.
- Write down at least three questions [use bullet points] that you
would ask BEFORE committing yourself to an investment in a business
proposition like SFJO.
- Continuing in the role of Moawia, assume that you have been in operation
for about a year; things seem to going along well, but a competitor has
opened a shop at the far end of the mall and you are worried that their
presence may drain business from your thus-far, successful operation. Use a
brief phrase to identify ONE specific course of action you might pursue to reinforce your
position as the "juice bar of choice" Al Ain Mall. What information
[use bullet points] would
you need to evaluate that potential course of action and what criteria would you use to
decide what to do? [again, bullet points]
- Assume that you have been in business for about a year. What specific
performance measures or indices would you want to have to determine whether
to stay in business or throw in the towel. Use bullet points to list five
different measures.
- Now assume that you are a lending officer at the bank that is going to
lend Moawia the money. Let's call it the
First, Last and Only Parkland Bank [FLOP].
- Write down three
questions that you would ask Moawia BEFORE agreeing to lend money to this fledgling
business. [More bullet points]
- Continuing in the role of the lending officer, assume that Moawia has
been in business for about a year. It is your job to evaluate the loan to
Moawia and assess the probability that the rest of the loan will be repaid
in accordance with the loan agreement. What performance measures or indices
would you want to have? Create a bullet point list of 5 measures.
- Review your answers to questions 3 and 4. How do the interests of Moawia and the bank converge? How are they
different?
- Identify the financial information provided in the case.
Use columns A and B of an Excel worksheet to create a list with amounts and
totals by category for the following:
- an investment in the business
- a one-time start-up cost
- a recurring expense
- revenue
- an asset, or
- a liability.
Stop here for now!
Assignment #2:
In order to adapt the Sombrero case for use in this class, I am incorporating
some assumptions; these assumptions are embedded in the assignment
questions below. Careful attention to these assumptions will make your
subsequent work easier and help to ensure that your answers are consistent. The
case is set in March 2002 and we'll assume that Moawia opens the shop
bright and early on Apri 1, 2002. We'll assume that Moawia was
really busy at the end of March and did all the things necessary
to open the shop within a few days. This is obviously unrealistic, but it
enables us to develop an analysis of the transactions involved and the impact of
those transactions on the balance sheet. Complete the following:
- As we discussed in class on September 14th, the case says nothing about
Moawi's equity stake in the business. Recall that the case says Moawia as no experience running a juice bar. With no experience
and nothing for Moawia at risk, no banker with any sense will lend him money at any reasonable interest rate. Therefore, we will
assume that Moawia's equity stake in the business is DH50,000. On March 29th
he goes to the bank and opens an account in the name of the Sombrero Fruit Juice Outlet
and deposits his DH 50,000. Prepare a balance sheet for Sombrero as of the end of the day on March
29th.
- Go to page 2 of the case and find the sentence that begins "The bank
would require interest of DH200 per month..." [it's the last sentence in
the short paragraph under the bulleted list of assets]. Substitute the
following information for that sentence: Assume that the loan from the
bank is a conventional mortgage. This means that the monthly payment is a
fixed amount, consisting of a declining proportion of interest and an
increasing proportion of repayment of principle. We'll assume that the
annual interest rate is 10% and that the mortgage is for 5 years. On March 30th, Moawia goes to the bank, gets the loan, and deposits the money in the
SFJO account. Prepare a balance sheet for Sombrero as of the end of the day
on March 30th.
- Now it is March 31st. Moawia buys all the equipment [he pays cash], hauls
everything to the site of the shop, sets everything up, hires and trains
the staff, and gets the utilities hooked up. By the evening of the 31st, Moawia is pretty tired, but he is ready to open the next morning. He
celebrates his accomplishments with an ice cream cone. Prepare a balance sheet as of the close of the day on March 31st.
Assignment #3:
Incorporate the following additional assumptions:
- Total interest paid on the mortgage for the first year is DH
1,854; the principle paid in the first year is DH 3,245.
- There are no accounts payable; all cash expenses for any month are paid
within the month, including the first month's rent, which will be paid on
April 1.
- Moawia operates the shop 7 days/week and is open 360
days/year.
- Moawia thinks that he can realistically expect to
serve 25,000 customers during his first year in business.
Required:
- Calculate Moawia's breakeven point in customers per year.
- Calculate Moawia's breakeven point in dollars of revenue per year.
- Suppose it is early October; Moawia has been open for a bit over 6
months. It's a "slow Monday" and Moawia is kind of worried about his
business. What can we do to make the results in 1a and 1b more useful to Moawia as an owner or manager?
- The calculations in requirements 1 and 2 focus on the
level of business needed to avoid an accounting loss. However, a
conversation with Moawia reveals that he is really more anxious about cash
flows. Recalculate the breakeven points in requirements 1 and
2 to find the cash
breakeven point.
- Consider the following scenarios:
- Moawia wonders whether he might do better if he
bought ingredients of a slightly higher quality. Suppose he can get
"premium" quality fruit for 10% more than he originally expected to pay. Because of the
increase in quality, Moawia expects that the customer volume would
increase, but he really isn't sure how many more customers he would
actually attract. He wants to know what increase in demand would be
necessary in order to be no worse off than with the lower quality and
original volume.
- Now suppose that Moawia wonders if paying his help a bit more might
have a positive impact on demand. He would like to increase salaries to
DH 1,500 per month. Once again, he is worried about the impact on profit
and the required increase in customer volume; maybe even with higher
salaries, his workers won't be aggressive enough at selling to offset
the increment in their wages. What happens to profit if the wages are
increased as suggested? What increase in customer volume is
necessary to maintain the prior net income? What is the percentage
increase in volume needed? Do you think this little scheme will work?
Why or why not?
Assignment #4:
Download the Sombrero template, which
is a .pdf file of two of my worksheets for the sombrero case analysis. You have
the numerical results for the break even analysis as well as the row and column
coordinates for my data and breakeven worksheets. Your task is to enter the
appropriate values and/or formulas to replicate these two worksheets. Note that the point here is not "just to
fill in the numbers;" we could train an intelligent 5-year-old to do that. You
will use your understanding of managerial accounting to derive the formulas that
will provide the correct results. The data worksheet is the entry
point for ALL of the basic data given in the case and all the assumptions about
the business. The breakeven worksheet is where I have summarized revenues, variable costs,
and fixed costs, calculated breakeven
points, and calculated a basic CVP graph. Note that in both the data and
breakeven worksheets, the only values [i.e., numerical entries, such as
12,000 for the leasehold improvements] are those that are highlighted in
yellow. All other entries are cell references [e.g., =B2/B3 or =C26].
Open a
new Excel workbook and rename one of the worksheets as data. Set up a worksheet like the
data worksheet in the Acrobat printout. ALL the raw data from the case will go in this worksheet. Note that
columns E and F of the data worksheet provide
space to indicate the source of the various pieces of information. On what page
[or in what exhibit] and in what paragraphs in the case
did you find this item? This is extremely useful when you have to go back and verify
something [and trust me, you WILL find yourself having to do this]. The SFJO case is “only”
3 pages; even in 3 pages, facts can get "lost" really easily. With a longer case, you can waste a tremendous
amount of time trying to locate facts that you “know are there
somewhere.” [Been there. Done that.]
Once you have finished the data worksheet, set up the breakeven worksheet.
To cell reference from one worksheet to another, use the worksheet
name, followed by an exclamation point, followed by the specific cell
from the source worksheet. For example, cell B2 in the breakeven
worksheet is =DATA!B30. This is where we really
start applying the concepts we are learning this semester, so your prior
attention to things like variable costs, contribution margin, and related
matters is especially important.
Stop here for now
Assignment #5:
- As you will see, my model consists of
eight worksheets. There is nothing
inherently "correct" about using eight worksheets, but the
combination of worksheets I have used provides
a convenient way of organizing the information. In principle there would be
nothing wrong with doing the entire analysis in a single large worksheet.
However, arranging the parts of the worksheet would be a challenge and
getting an organized printout would be difficult. The eight worksheets are:
- mtge [the calculation of Moawia's mortgage amortization
table and the total payments of principle and interest he will make each
month and year]
- cash [calculation of the cash balances as of the end of the
day on March 29th, March 30th, and March 31st, and the end of his first
fiscal year]
- transactions [a transaction-by-transaction summary of
Moawia's business for year 1, including those that took place prior to
opening for business]
- income [income statement the first year of the shop's
operations. The
case is set in Abu Dhabi Emirate and monetary amounts are in Dirhams;
therefore, the income worksheet includes a section that
converts the income statement to US dollars so that we have a realistic
reference point in evaluating the performance of the business]
- balance [balance sheets as of the end of the day on March 29th,
March 30th, and March 31st, and the end of his first fiscal year]
- perf [performance evaluation--a summary of ratios and other
statistics to evaluate the operation and viability of the business]
- Notice that this series of worksheets provides a very comprehensive
analysis of Moawia's business. Breakeven analysis, profitability, cash flow,
financial ratios and end-of-year financial statements are all available
within the same Excel file, and all derived from the same basic data, thus
ensuring consistency among the various analyses. You may observe that some
items appear more than once [net income and net cash flow, for example].
This is quite intentional. Approaching things from different perspectives
helps to ensure that the results are consistent and it also demonstrates
that you really do understand the interrelationships between and among
financial variables
- The information given in the case about revenues, cost of
goods sold and operating costs are projections. Actual
experience may reveal that things are somewhat different [or perhaps, very
different] from what Moawia is expecting. However, based on what he thinks
will happen, we can prepare projected financial statements for
the fiscal year ending 3/31/03. Use T-accounts to record the transactions
implied from the facts and projections in the case. Make aggregate entries
where appropriate. For example, total cash fixed operating expenses for the
year are DH 154,800; debit operating expenses and credit cash for that
amount rather than making individual entries for specific months and/or
expense items.
- prepare an income statement
- prepare an analysis of cash flows
- prepare a balance sheet
Assignment #5:
- Download the sombrero template.xls and the
sombrero.pdf file. The Excel
template is designed to speed your worksheet setup process. All the labels
have been entered for you. The Acrobat file is a printout of my Excel
model;
- Create a new worksheet; rename it mtge and set up the mortgage amortization table.
- The periodic
payment in cell D6 is found using the Excel =PMT function; the arguments for
the function are the interest rate, the number of periods, and the amount
borrowed. If you enter the amount borrowed as a positive value, Excel
will return a negative number for the periodic payment. I prefer to use
a negative value for the amount borrowed so that the payment is a
positive number. It really doesn't matter how you do it; just be sure
you use an appropriate sign when you use the periodic payment in
subsequent calculations.
- Because we are assuming monthly payments, we need a monthly interest rate, which is calculated in cell E4.
- Note that the shaded
rows [rows 22, 34, etc.] are not totals; I have highlighted them simply to mark
off the last month of each year. We'll extract the totals for the respective years using the
Excel database functions. [See below.]
- When you get down the the 60th month in the table, the amount
in the "balance due" column should be essentially zero; mine
actually comes out to
0.0000000001110720....
- EVERYTHING should be
cell-referenced so that if you decide to change something in your DATA
worksheet [e.g., he borrows DH45,000 instead of DH20,000, or the interest rate goes to 12%], the
entire mortgage table will recalculate.
- Note the use of the Excel DSUM function to calculate the total principle and
interest payments in year 1. Note the following pointers about the database
functions:
- The database is the entire range from which we
want to extract information--A10:E71 if you set up the table the way I did. Note
that the database includes the header row. The header row must be a
single row, and each column [field] in the database must have a different
heading.
- The criteria range consists of the same column 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. The criteria
range for year 1 is A74:E75; enter a 1 in A75. Enter
the DSUM
formulas in the output row [cells D76 and E76] to extract the
principle and interest information for year 1. To get a feel for how this works, erase the
1 in cell
A75 and put a 4 in
cell B75; Excel will extract the sum of the payments for
all the "Aprils" [April
year 1 + April year 2....]. [If your table does not have row-column
coordinates that are identical to those in the illustration you will have to
change the DSUM formulas accordingly.]
- To extract the principle and interest for year 2 [or year 3 or
whatever], you could just change
the value in A75, but then you would lose the information about year 1.
We don't need the principle and interest information for the others
years, but take advantage of this little example to get practice
with the DSUM functions. Set up the criteria and
output ranges for years 2, 3, 4, and 5 so that you can calculate the
total interest and principle
paid over the life of the loan. The total principle paid should be
DH20,000--the amount Moawia borrowed. The total
interest paid over the life of the loan provides an indication of the "real" cost of borrowing.
- Set up the cash worksheet. Derive cash balances for March 29th,
30th and 31st and for the end of the first year of operations as shown in my
worksheet.
- Now set up the income worksheet. Once you have the income
statement and the reconciliation with cash finished, let's test the model
and see what happens to net income if the anticipated customer volume
changes.
- In cell C23 of the income worksheet, enter 2,500 as
the monthly volume. Recalculate the annual revenue, variable
costs, contribution margin, fixed costs and net income in cells C24:C28.
Cell reference the anticipated profit from your income statement in C29,
and then find the increment or decrement in C30.
- Now let's "prove" the numbers we have just found. Cell reference the
monthly and annual customer volumes in C33:D33, respectively; cell
reference the original monthly and annual volumes in C34:D34. Find the
difference in C35:D35. Cell reference the contribution margin per
customer in C36:D36, and then find the incremental contribution in
C378:D37.
- Now change the value in C23 to 2,501. What will be the impact of a 1-person change in the
anticipated customer volume? In other words, what is one
more person "worth" to Moawia's business?
- It is important to understand relationships like these because you
can use them to verify that calculations are consistent.
- Once you have finished the income worksheet, you can do the balance
worksheet. The four balance sheets correspond to the four cash balances you
calculated above.
- Complete the perf [performance evaluation worksheet. What do you
think about the potential for the shop? If Moawia asked for your
professional advice about the economic viability of the Caribbean Internet
Shop as currently envisioned, what would you tell him?
- We have now set up a financial model for the Sombrero Fruit Juice
Outlet.
- Sensitivity analysis is an important element in decision making.
Sensitivity analysis is a way of asking something like "How far off do my
estimates have to be to change the decision I would otherwise make?"
Consider the following scenarios:
- Moawia wonders whether he might do better if he
bought ingredients of a slightly higher quality. Suppose he can get
"premium" quality fruit for 10% more than he originally expected to pay. Because of the
increase in quality, Moawia expects that the customer volume would
increase, but he really isn't sure how many more customers he would
actually attract. He wants to know what increase in demand would be
necessary in order to be no worse off than with the lower quality and
original volume. To do the analysis required, proceed as follows:
- It will be useful to be able to change the fruit cost in the data
worksheet and see the impact on profit immediately. In your data
worksheet, cell reference the net income so that you can see it change
as the fruit cost changes.
- Now factor the fruit cost up by 10%. Your net income should drop to
DH 27,406. [Can you provide a numerical reconciliation of the difference
in income?]
- Now use a trial-and-error process to determine the change in
customer volume needed to ensure that Moawia is no worse off with the
higher quality fruit. A few changes in the customer volume cell [DATA!B46]
should enabler you to zero in on the required volume. How may more
customers will Moawia have to attract? What other way could you use to
find the answer?
- Before continuing, change your spreadsheet so that fruit cost and
customer volumes reflect the original assumptions.
- Now suppose that Moawia wonders if paying his help a bit more might
have a positive impact on demand. He would like to increase salaries to
DH 1,500 per month. Once again, he is worried about the impact on profit
and the required increase in customer volume; maybe even with higher
salaries, his workers won't be aggressive enough at selling to offset
the increment in their wages. Replicate the steps in a-i, a-ii, and
a-iii above, substituting the increment in salaries for the increment in
food costs. What happens to profit? What increase in customer volume is
necessary to maintain the prior net income? What is the percentage
increase in volume needed? Do you think this little scheme will work?
Why or why not?
|
