Question
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...Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started