Lucy's Gourmet Basket shop is planning to open for business on July 1, 20XX and she is concerned about the survivability of her company. She has contacted you to prepare operating budgets for the quarter ending September 30, 20XX. She would like the budgets set out in monthly and quarterly columns for this time period. This business is a merchandising business so you do not need to worry about the manufacturing of the gourmet baskets.
July ........... 7,000
August ........ 9,000
September ........ 12,000
October........ 10,000
Selling Price ......... $59
Ending inventory should be .. 19% of next month’s sales demand in units
Beginning Inventory, July 1 .... 0
Purchase price of baskets ........ $33
Payment of purchases ............. 47% Paid in month of purchase
53% Paid in month after purchase
Projected Cash Sales and collection history of the credit sales is as follows:
24% Cash sales in the month of sale
49% Credit Sales Collected in following month***based on total sales revenue for the month
26% Sales Collected in Second month following sale
1% Is deemed uncollectible and therefore will not be shown as cash inflow but is factored into the calculation of net sales
****Variable operating expenses are based on number of units sold
****All selling, general, and administrative expenses are paid in the month incurred
In August, the company plans to purchase equipment using cash.
They plan to pay cash of: ........... $20,000

At the present time, the company does not have a required minimum balance and does not have an open line of credit with any bank. Therefore, you do not need to show these sections on your cash budget.
Planned beginning balance in cash ............ $21,000
Prepare the following budgets for the three-month period ending September 30.
The text illustrates an annual budget, but you will be preparing a quarterly budget.
Develop your budgets on the sheet entitled budget. Use this sheet as your data for all of your cell referencing.
1. A cash receipts budget from sales, by month and quarter.
2. A merchandise purchases budget in units and in dollars, by month and quarter.
I have set up a formatted budget for you. You can see how I have adapted this budget to Schedule 2 on page 356.
This company is not producing the product but must buy the baskets to sell to customers. Make sure you think about the numbers you use in the quarter column. I am specifically talking about how you handle beginning and ending inventory, since the BI and EI should not be added across.
3. Prepare a Selling, General, and Administrative Expense Budget, by month and quarter. See page 361
4. A Cash Disbursements Budget, by month and quarter.
5. Prepare a cash budget showing the months and quarter.
You can reference schedule 9 page 365, but I have this budget set up differently to track the cash balance monthly and quarterly.
There is no loan situation, since we are trying to develop a cash management plan and give a recommendation in #8 below.
6. Based on the quarterly cash budget you prepared, do you have any recommendations on cash management? You should be thinking about the cash position as they go into the next quarter.
7. Prepare a budgeted income statement for the quarter ending September 30, 20XX.
You do not need to prepare a COGS since this is a merchandising business and the COGS can be determined by units sold t times purchase price of basket.
I have entered COGS for you. See Schedule 11 on page 366.
8. What do you think about the survivability of this business?
9. What if the company finds out the monthly rent will increase to $3,000, what budgets are affected? Why?
What is the New Net income (Loss)?
If you have linked everything correctly, you should only have to change the monthly rent on this sheet to determine your answer to the questions asked.

  • CreatedSeptember 19, 2013
  • Files Included
Post your question