View the step-by-step solution to:

Question

Moore's Spreadsheet - Final Homework.xls

src="/qa/attachment/10755083/" alt="Cash Flow Tips Final Homework Assignment.xlsx" />

A. Past & Current Operations

"Moore's Family Restaurant" is a restaurant on the edge of the downtown area in a medium-size, eastern city. It is on one corner of a major federal highway and a busy cross-town state highway with easy access to the interstate. The restaurant has a reputation for good "home cooking'" at reasonable prices. It provides an ample but simple "1920's/1930's" sit-down area for eating. Many of its customers have been coming here for years although most of them have moved to the suburbs so fewer stop in as much anymore.

The restaurant was founded in 1916 by Sam Moore who ran it until his son George took over in 1945. George Moore turned the business over to his son, George, Jr. in 1975. For the next 20 years or so George Moore, Jr. ran the business with his brothers and a sister until they lost interest in the business. The last of Mr. Moore's siblings relinquished any ownership in the business to Mr. Moore in 1997.

The restaurant has always served meals prepared from fresh, home-made and local products obtained from farms, provision houses and local merchants. The restaurant has an active alcohol beverage license, but no longer uses it. The restaurant stopped serving alcohol in the '70's when the neighborhood began to change. Furniture, fixtures and equipment are old, but fully serviceable and reasonably efficient for the business volume. Despite its age and use many customers comment on the "charm" of the decor and how the "place never changes." The restaurant has had no difficulty with health authorities, customers or neighbors.

Since the restaurant was founded the downtown area has changed from one thronged with neighborhood residents, shoppers, factory and warehouse workers to one with a dwindling number of office workers, government workers and transient motorists. During this same period the number of family-owned and -operated restaurants has declined steadily, being replaced by chain restaurants and up-scale "white table cloth establishments." From being the only restaurant in the neighborhood Moore's Family Restaurant now has competition: Wendy's, KFC, and Taco Bell are on the other 3 corners of the intersection at which it's located.

As a result of these changes, changes in consumer preference and other reasons, business and profits have been on the decline for several years. In the last 2-3 years this decline has been severe. In 2016 Mr. Moore asked his son, Tom, who is an executive with a local company and a Wilmington University MBA, for a "strategic plan" for the restaurant. Tom, in turn, asked some friends of his, a team of consultants with whom he worked, to "come in and take a look at the business in order to help out my dad."

The consulting team recommended that Mr. Moore take advantage of the "old fashioned" decor and Moore Family Restaurant's reputation to convert it into an upscale "theme" restaurant. They also made these recommendations:

·        Re-appraise the value of the land, building and fixtures to reflect current values (increases in the 30+ years since the values on the balance sheet were set). Apply for an "Inner City Renovation Grant" for preferential loan and tax rates for renovated property in the city's core.

·        Activate the liquor license, refurbish the restaurant, put in a 1920's "speakeasy" bar, but don't change the "antique" appearance.

·        Take out a revolving line of credit (which it could draw upon if needed) of up to $100,000 against the re-appraised value of land and buildings to pay for the improvements. This is costing him 3% per year since interest rates are so low and is expected to remain at 3% during the 10 year draw period. For now he won't have to pay any loan balances off, just interest only payments during the initial draw period.

·        Change the source (but not the nature or quality) of food, supplies and services to reduce costs; upgrade and professionalize the labor (cooks and servers); revise pricing and the "business model".

·        Continue to accept credit cards (Master Card, Visa, American Express & Discover) for 50% of meals are charge sales. Use 365 as the number of "selling days" per year to coincide with bank practice and calculations.

·        Consider opening up a catering business as a new revenue stream to reach out to new Customers.


Mr. Moore followed all the recommendations, which resulted in the financial statements in the excel spreadsheet that accompanies this exam.

Use the data in the excel document accompanying this exam to compute these ratios for 2012 in the Final Exam Test (next item on the Blackboard site). You may find this worksheet helpful to make your calculations and review them before submitting them to the actual test in Blackboard.



B. Potential Retirement

Through good years and not so good, Mr. Moore maintained that he enjoyed the business and wanted to stay in business as long as possible, particularly in order to participate in the current economic boom. But, Mr. Moore is in his 60's. He is concerned about the business' future as well as his own. On the one hand he says he would like to "run the business as always, for as long as I'm able"; on the other hand he has been heard to say "it gets harder all the time. Maybe I should just pack it in."

At the end of 2016 Mr. Moore asked if he would be able to retire at that time. The consulting team evaluated selling the business for its book value (Owner Equity). The team would use the Owner Equity in the business realized from a sale in order to buy an investment that would pay Mr. Moore 3% per year (paid annually in one payment). At the end of 2016 Mr. Moore was 61 years old. It was suggested that he plan to live until he is 100 to make sure his money lasts, thus the money would be invested for 40 years. Mr. Moore said he'd consider retiring if the investment could produce $100,000 per year for his retirement so he can indulge in his passions - badminton, bocce and butterflies.


C. Ongoing Operations

Use data below for questions 17-23 in this section and the data in the MS Excel attachment to calculate a budget for 2019. Hint: You may find it helpful to have a "standard" P&L which states all data as a percent of sales. Some "planning" questions will provide all needed data in the questions themselves.

Mr. Moore believes that roughly 8% more meals can be served in 2019 (due to the new strategy kicking into high gear) so he plans to serve 272,514 meals in 2019. He also plans to adjust prices so an average meal will be $7.35 per meal. Hint: "Sales" have been calculated as number of meals served x average price per meal.

The consulting team believes that the cost of product (calculated as a percent of sales) can be 35% of sales in 2019. Mr. Moore wants to keep the good staff he hires and so is planning to give "cost of living raises" of 2.5% over 2018 in the next year. Benefits will remain at the current 35% of the labor cost next year. The utility companies have advised all business customers that rates will increase by 15% next year.

According to the loan agreement, there will be no repayment of principle on the revolving line of credit (since we are currently in the draw period). Hence payments being made are just interest payments which are expected to be $3,000 total in 2019.* Because of agreements with the city and the insurance company (from whom he received his loan) taxes and insurance will remain the same in 2019.* Likewise depreciation expense will remain the same as in 2014.*

Service costs are expected to grow by 15% next year plus an added $55,000 is to be budgeted for extra accounting services. Other Expenses (GSA, advertising and promotion and other) are expected to increase by 10% from 2018. The heavy advertising of the new restaurant should not be necessary and there's been time to plan for some other efficiencies. The income tax rate is expected to be 28% of DEBIT which is reduced for interest and depreciation, the same percent of DEBIT as in 2018.


D. Retirement Revisited (Questions 24 and 25)

Assume Mr. Moore operates the "Re engineered Business" until the end of your budget period (2019). At that time he would sell the business for its book value (Owner Equity). It is estimated that the Owners Equity would grow to at least $1,825,684. Mr. Moore would use this amount to buy an investment that would pay him 6% per year (paid annually in one payment). At the end of 2019 Mr. Moore will be eligible for Social Security payment of $14,500 per year at that time.

It was suggested that he plan to live until he is 100 to make sure his money lasts, thus the money would be invested for 40 years. Mr. Moore said he'd consider retiring if the investment and Social Security produced a total of $150,000 per year for his retirement.



* These values have been entered on the worksheet; there is no need to calculate these amounts. Calculate and enter only those amounts on the exam with question numbers' shown.

1.   The total adjustments to net income to determine cash flows from operating activities            are......(Hint: be sure to add up changes in current assets and current liabilities as noted on the balance sheet going from year 2017 to year 2018) to get the answer.

 

2.   Net cash flows from operating activities are....(hint: don't forget about net income).

 

3.   Cash flows from investing activities are.....

 

4.   Net Cash flows from investing activities are....

 

5.   Cash flows from financing activities are...(hint: since this is a partnership common and preferred stocks and bond financing does not exist).

 

6.   Net increase/decrease in cash flows is....


7.   What is to be the Sales budget for 2019?

8.   What is to be the Cost of Product for 2019?

9.   What is to be the Labor budget for 2019?

10. What is to be the Benefits budget for 2019?

11. What is to be the Cost of Services for 2019?

12. What are Total Costs to be budgeted for 2019?

13. What are Earnings before Interest, Taxes and Depreciation (DEBIT) to be for 2019?

14. Mr. Moore is considering selling the business at the end of 2019 for his Owners Equity (projected to be at least $1,825,684) and using that amount to buy an investment that would pay him 6% per year (paid annually in one payment) for 40 years. What would the annual payment from such an investment be?

15. Will the combination of investment return and Social Security payments described in the text of the case meet Mr. Moore's minimum retirement goals?

1)   No, it will fall short of his goal by approximately $14,160

2)   Yes, it exceeds his goal by approximately $14,160

3)   Yes. It exceeds his goal by well over $40,000.

4)   Not enough information to tell.

Cash Flow Tips Final Homework Assignment.xlsx

10. What is the net income figure to use as a starting point for the statement of cash flows for the year ending
Earnings After Interest, Income Taxes, & Depreciation from 2018 Remember to always start with net income, from there on the csah flow statement a cash flows for the year ending 2018? he csah flow statement adjustments are made to get a better sense of the company's actual cash po y's actual cash position. 11. The total adjustments to net income to determine cash flows from operating activities Cash flows from operating activities:
Net income (earnings after taxes)
Adjustments to determine cash
flow from operating activities:
Add back depreciation
Increase in accounts receivable
Increase in inventory
Increase in prepaid expenses
Increase in accounts payable
Increase in Wages and Benefits due
Increase in estimated taxes and fees
Total adjustments
Net cash flow from Operating Activities are……(Hint: be s ities are……(Hint: be sure to add up changes in current assets and current liabilities as noted on the balance sheet goin Net income figure goes here depreciation added back here I am giving you here the changes in current
assets and liabilities. It is up to you to figure out
what the numbers are and whether or not they
are positive or negative. Remember that
increases in assets is money out of your pocket
while increases in liabilities is money in your
pocket.
The total adjustements at the end adds up all
the positive/negative numbers included as
part of the operating activities section of the
cash flow statement. This is the figure in
yellow, that is the answer you are calculating
here. This gets back to the cash flow statement
mentioned in week 2, so it may be a good idea
to review those notes. ed on the balance sheet going from year 2017 to year 2018) to get the answer. 12. Net cash flows from operating activities are....(hint: don’t forget about net income). Cash flows from operating activities:
Net income (earnings after taxes)
Adjustments to determine cash
flow from operating activities:
Add back depreciation
Increase in accounts receivable
Increase in inventory
Increase in prepaid expenses
Increase in accounts payable
Increase in Wages and Benefits due
Increase in estimated taxes and fees
Total adjustments
Net cash flow from Operating Activities I am giving you here the changes in current
assets and liabilities. It is up to you to figure out
what the numbers are and whether or not they
are positive or negative. Remember that
increases in assets is money out of your pocket
while increases in liabilities is money in your
pocket.
The total adjustements at the end adds up all
the positive/negative numbers included as part
of the operating activities section of the cash
flow statement. Don't forget to add in
depreciation and your net income figures here.
Note the answer in yellow that you are
calculating is different here than in the
previous problem. This gets back to the cash
flow statement mentioned in week 2, so it may
be a good idea to review those notes. 13. Specifically, when lookinat the cash flows from investing activities, what value should be entered for an increase Cash flows from investing activities:
Increase in buildings
Increase in equipment
Increase in fixtures Net cash flows from investing activities e entered for an increase in building investments? I am giving you here the changes in cash flows
from investing activities. It is up to you to figure
out what the numbers are and whether or not
they are positive or negative. Remember that
increases in assets is money out of your pocket
while increases in liabilities is money in your
pocket.
Here note the number in yellow and what is
being asked for, just the building figure. As in
previous sections you have to calculate this
based on how the balance sheets change from
year to year. This gets back to the cash flow
statement mentioned in week 2, so it may be a
good idea to review those notes. sh flows
u to figure
er or not
ber that
ur pocket
n your what is
re. As in
te this
nge from
h flow
may be a 14. Net Cash flows from investing activities are.... Cash flows from investing activities:
Increase in buildings
Increase in equipment
Increase in fixtures Net cash flows from investing activities I am giving you here the changes in cash flows from
investing activities. It is up to you to figure out what
the numbers are and whether or not they are positive
or negative. Remember that increases in assets is
money out of your pocket while increases in liabilities
is money in your pocket.
Here note the number in yellow and what is being
asked for, the net cash flows from investing
activities. This means that you have to take a look at
how buildings, fixtures and equipment collectively
change and sum up all the values. As in previous
sections you have to calculate this based on how the
balance sheets change from year to year. This gets
back to the cash flow statement mentioned in week 2,
so it may be a good idea to review those notes. n cash flows from
o figure out what
ot they are positive
ases in assets is
creases in liabilities nd what is being
investing
ve to take a look at
ment collectively
As in previous
s based on how the
to year. This gets
entioned in week 2,
those notes. 15. Cash flows from financing activities are... Cash flows from financing activities:
Increase in bonds payable
Preferred stock dividends paid
Common stock dividends paid
Net cash flows from financing $0.00
$0.00
$0.00
$0.00 Here is a hint....these values are zero because Mr. Moore is running
owners other than him!!!! Remember, this is a family-owned busin
at this point. Hence, there are no stocks or bonds people are buyin
revenue. If there were, then there would be values for these numb e Mr. Moore is running a small business with no
a family-owned business with no other investors
onds people are buying as alternative sources of
values for these numbers. 16. Net increase/decrease in cash flows is.... Cash flows from operating activities:
Net income (earnings after taxes)
Adjustments to determine cash
flow from operating activities:
Add back depreciation
Increase in accounts receivable
Increase in inventory
Increase in prepaid expenses
Increase in accounts payable
Increase in Wages and Benefits due
Increase in estimated taxes and fees
Total adjustments
Net cash flow from Operating Activities
Cash flows from investing activities:
Increase in buildings
Increase in equipment
Increase in fixtures
Net cash flows from investing activities
Cash flows from financing activities:
Increase in bonds payable
Preferred stock dividends paid
Common stock dividends paid
Net cash flows from financing
Net increase (decrease) in cash flows Net Income from the 2018 income statement depreciation from the 2018 income statement
Answer calculated based on your work from problems 11 and 12
Answer calculated based on your work from problems 11 and 12
Answer calculated based on your work from problems 11 and 12
Answer calculated based on your work from problems 11 and 12
Answer calculated based on your work from problems 11 and 12
Answer calculated based on your work from problems 11 and 12
Answer calculated based on your work from problems 11 and 12
Answer calculated based on your work from problems 11 and 12 Answer calculated based on your work from problems 13 and 14
Answer calculated based on your work from problems 13 and 14
Answer calculated based on your work from problems 13 and 14
Answer calculated based on your work from problems 13 and 14 Answer calculated based on your work from problem 15
Answer calculated based on your work from problem 15
Answer calculated based on your work from problem 15
Answer calculated based on your work from problem 15 The answer for this problem is in yellow, this is summing up all the values from what you calculated in problems 11,12,13,14 ated in problems 11,12,13,14 and 15

Moore's Spreadsheet - Final Homework.xls

Moores Familay Restaurant Financial Data
Income & Expense Statement
Sales
Cost of Product
Labor
Benefits
Utilities
Loan Principle Repayments
Insurance
Property Taxes
Services (accounting, trash, cleaning, etc.)
Other: SG&A, advertising, promostions
Total Costs
Earnings Before Interest, Income Taxes, & Depreciation (EBITD)
Interest on loan
Income Taxes
Depreciation on values
Earnings After Interest, Income Taxes, & Depreciation
Meals Sold
Average Meal Value Historic Performance (Old Business Mode
2013
2014
$1,794,000 $1,677,120
$485,000 $714,250
$298,600 $294,856
$46,269
$48,651
$58,500
$61,425
$35,000
$65,000
$7,560
$8,505
$20,000
$21,000
$6,800
$13,600
$957,729 $1,227,287
$836,271 $449,833
$234,156 $125,953
$602,115 $323,880
312,000.00 279,520.00
5.75
6.00 ormance (Old Business Model)
% Change
2015
2016
2013-2016
$1,635,750 $1,620,060
-9.70%
$784,963 $797,211
64.37%
$292,516 $330,772
10.77%
$48,265
$57,647
24.59%
$58,047
$67,721
15.76%
n/a
$100,000 $115,000 228.57%
$8,505
$9,072
20.00%
$25,000
$29,000
45.00%
$27,200
$35,360 420.00%
$1,344,496 $1,441,783
50.54%
$291,254 $178,277 -78.68%
n/a
$81,551
$49,918 -78.68%
n/a
$209,703 $128,359 -78.68%
261,720.00 249,240.00 -20.12%
6.25
6.50
13.04% Moores Familay Restaurant Financial Data
Budget Worksgheet
Sales
Cost of Product
Labor
Benefits
Utilities
Loan Principle Repayments
Insurance & Property Taxes
Services (accounting, trash, cleaning, etc.)
Other: SG&A, advertising, promostions
Total Costs
Earnings Before Interest, Income Taxes, & Depreciation (EBITD)
Interest on loan
Income Taxes
Depreciation on values
Earnings After Interest, Income Taxes, & Depreciation
Meals Sold
Average Meal Value 2017
$1,642,896
$697,211
$330,772
$79,872
$54,340
$0
$110,000
$41,051
$77,629
$1,390,875
$252,021
$3,500
$70,566
$71,176
$106,779
243,392
6.75 2018 (Present Day)
$1,766,296
$597,211
$340,695
$114,519
$50,644
$0
$110,000
$43,908
$84,771
$1,341,748
$424,548
$3,000
$118,873
$61,777
$240,897
252,328
7.00 2019 Budget Forecast
Answer to number 17
Answer to number 18
Answer to number 19
Answer to number 20
$58,241
$0
$110,000
Answer to number 21
$93,248
Answer to number 22
Answer to number 23
$3,000
#VALUE!
$61,177
#VALUE!
272,514
7.35 Moore's Family Restaurant
Assets & Liabilities as of 12/31/17
ASSETS
Cash
Accounts Receivable
Inventories
Prepaid Expenses
Total Current Assets $492,118.00
$29,000.00
$30,000.00
$2,000.00
$553,118.00 Building
Equipment
Fixtures $727,460.97
$14,500.00
$33,320.00
Total Fixed Assets Total Assets $775,280.97
$1,328,398.97 LIABILITIES
Accounts Payable
$57,432.00
Wages and Benefits Due
$16,516.59
Estimated Taxes and Fees
$6,013.00
Total Current Liabilities
$79,961.59
Long Term Loan
$70,000.00
Total Long-Term Liabilities
$70,000.00 Owner Equity
Total Liabilities and Owner Equity $1,178,437.38
$1,328,398.97 Assets & Liabilities projected to 12/31/18
ASSETS
Cash
Accounts Receivable
Inventories
Prepaid Expenses
Total Current Assets $589,118.00
$49,095.00
$30,000.00
$11,000.00
$679,213.00 Building
Equipment
Fixtures $760,000.00
$18,000.00
$35,500.00
Total Fixed Assets Total Assets $813,500.00
$1,492,713.00 LIABILITIES
Accounts Payable
$137,566.00
Wages and Benefits Due
$29,543.00
Estimated Taxes and Fees
$8,013.00
Total Current Liabilities
$175,122.00
Long Term Loan
$70,000.00
Total Long-Term Liabilities
$70,000.00 Owner Equity
Total Liabilities and Owner Equity $1,247,591.00
$1,492,713.00

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask Expert Tutors You can ask You can ask ( soon) You can ask (will expire )
Answers in as fast as 15 minutes