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 1^st 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 4^th 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 1^st 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.

Answer rating: 100% (QA)

Sales Budget Oct2017 Nov Dec Total Sales in 550004 220000 220000 220000 660000 Cash Sales 45 99000 9... View the full answer