Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Abby Gold finger has a headache. It is 6 months since she acquired the Philly Roasters coffee shop on Walnut Street in Philadelphia. It has

Abby Gold finger has a headache. It is 6 months since she acquired the Philly Roasters coffee shop on Walnut Street in Philadelphia. It has taken her all this time to learn the nuts and bolts of the business so that she now feels comfortable making plans for improving performance. Unfortunately, in the six months, sales have been declining versus the same period a year ago, and below the level she assumed when she bought the business. She has made successful business investments in the past, and is determined to make a resounding success of Philly Roasters. However, she has no experience in foodservice operations nor in managing a largely cash business.

Abby’s plan is to manage the shop herself initially, improve its performance over the next 12 months and then hire a seasoned manager who will allow her to step back and look for another venture.

In 2016, (prior to Abby purchasing the business) the financial results of Philly Roasters were as follows:

$’000s
Cash sales
Credit card sales
Total
Gross Receipts
$300
$400
$700
Card company payments

8
8
Net Sales
300
392
692
COGS (variable costs plus wastage)
100
133
233
Gross Profit
200
259
459
Selling & Admin Costs


275
Operating Profit


184
Interest expense


8
Earnings before taxes


176
Taxes


44
Net Income


132

Note: the owner’s remuneration is not included in SG&A expenses. SG&A includes the employment costs of the baristas (wages and benefits), rental expense for the shop and equipment and other general expenses.

The store is open from 7:00 am to 8:00 pm seven days a week. There are 2 staff on site at all times, with a total complement of 4 full time employees. The working schedule rotates over time so that the employees take turns to work early, late, weekends etc. On average each employee is working 46 hours a week. The owner is present during busy periods.

It is September 1st 2017, and time for Abby to go to the bank and request a continuation of the line of credit that Philly Roasters has been using to manage cash flow ups and downs. To get ready for the meeting she must prepare an income statement and cash flow budget for the 4th quarter including monthly budgets for October, November and December. Following are the key assumptions that Abby will use for the budget based on her observations since acquiring the business:

Sales $ 55,000 per week of which 45% are cash sales and 55% are credit card sales

Credit card sales are collected from the bank at the end of the month following the sale (e.g. august credit card sales are collected at the end of September)

Cost of goods sold same % sales as full year 2016

Barista wages $12 per hour plus 35% benefit costs payable end of each work week

Real estate rent $ 4000 per month payable first day of month

Equipment rental $ 3000 per month payable end of month

Supplies $1000 per month payable 20 days after month end

Marketing $ 6000 in December only payable in December

Interest rate on credit line 6% per annum payable monthly in arrears

Tax rate 25% of pre-tax income payable, payable 30 days after quarter end.

Philly Roasters maintains a 4 week inventory of coffee, cups, lids and carrying trays based on next month’s forecast sales. These items are bought for cash from a cash and carry supplier.

Abby estimates that October 1st balances will be:

Cash balance $ 5000

Receivable from credit card banks $35,000

Materials inventory $ 18,000

Total assets $ 58,000

Payables $ 1000

Income tax payable $ 12,000

Outstanding balance on line of credit $20,000

Shareowners equity $ 25,000

Total liabilities and shareowners’ equity $ 58,000

Required:

Prepare the following budgets for each month October, November, December and the full quarter in an easily readable form in excel with proper use of formulae and formatting:

(a)Prepare a receipts and disbursements cash flow forecast. How much does the shop need to borrow at the end of each month?

(b)Prepare a budgeted income statement

(c)Prepare a budgeted balance sheet at December 31st

Make sure your answers are highlighted. Formatting is as important as the correct answer for this project. You will hand in spreadsheet(s) on the due date presented and printed clearly and labeled appropriately. Each budget needs to fit on one printed page. You must use formulae, no plugging answers.

*****FORMATTING: This should be done in excel, in spreadsheet form. It can be multiple excel files, or multiple sheets in 1 excel file. Formulas must be used for calculations where necessary, no plugging in numbers. You can copy and paste the excel sheets here if you cannot upload the excel file, formulas also included.

Additional problem info that may be helpful: Roasters has $ 20,000 of debt from the credit line. If there was zero cash flow this balance would remain the same and interest expense would be 6% divide by 12 times 20,000 per month. You should find that there is positive cash each month and this can be used to pay down the debt. Therefore the interest will decline each month. You should develop a reasonable methodology for calculating interest each month.

Step by Step Solution

3.43 Rating (150 Votes )

There are 3 Steps involved in it

Step: 1

Sales Budget Oct2017 Nov Dec Total Sales in 550004 220000 220000 220000 660000 Cash Sales 45 99000 9... blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Business Communication Essentials a skill based approach

Authors: Courtland L. Bovee, John V. Thill

6th edition

978-0132971324

More Books

Students also viewed these Accounting questions

Question

Why are segmentation andpaging sometimes combined into one scheme?

Answered: 1 week ago

Question

Calculate the percentage change in utilization for the two months.

Answered: 1 week ago