Accounting question attached

Accounting question attached

ATTACHMENT PREVIEW

Download attachment

Problem 4 worth 15 pts.
Save you file using your first initial, last name, and name of problem.
Due by Tuesday, May 7th by 11:59 p.m.
Upload to step 2 of this assignment.
This problem covers material in chapter 9 and there are extensive budget demonstrations in the chapter 9 course documents folder.
I have a template set up on the solution worksheet that you should use to complete the required budgets that are stated on the solution worksheet tab.
You need to use cell references in the development of your budgets.
You must use this worksheet to reference the data that is being inputted onto the budgets on the solution worksheet.
The example in the textbook is for a manufacturer and this turn in problem is for a merchandiser, which should be easier to budget for since the company is not making the units.
There is an extensive demonstration of a merchandising
budget development in the chapter 9 folder in course documents that you should review before attempting this problem.
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
so you do not need to worry about the manufacturing of the gourmet baskets.
Sales:
July
7,000
August
9,000
September
12,000
October
10,000
Selling Price
$59
Ending inventory should be
19%
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
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
100%
Fixed
Variable
Sales Personnel Salaries
$4,000
****All selling, general, and administrative expenses are paid in the month incurred
Sales Commission
$2
Media Advertising
$1,000
Facility Rent
$1,800
Insurance related to operations
$300
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. See page 362 Schedule 7
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.
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.
See page 363 Schedule 8
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.
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?
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.
Name:
Type your name here
You should use this sheet as your data field and
only use cell reference
s and formulas in your budgets.
Your grade will be based on accuracy of your solution and
correct usage of excel. The solution worksheet has formatted budgets for you to complete.
quarterly columns for this time period.
This business is a
merchandising
business
of
next
months sales demand in units
Projected Cash Sales
and
collection history of the credit sales is as follows:
Monthly
Selling , general, and administrative expenses are estimated using the flexible budgeting formula as follows:
****Variable operating expenses are based on
number of units sold
In
August
, the company plans to purchase equipment using cash.
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.
7.
Prepare a budgeted income statement for the
quarter
ending September 30, 20XX.
9.
What if the company finds out the monthly rent will increase to $3,000,
what budgets are affected? Why?

Unlock Solution Unlocking…

#1
Lucy’s Gourmet Basket Inc.
Cash Receipts Budget
For the quarter ending September 30, 20XX
July
August
September
Quarter
Sales Revenue
Cash sales in the month of sale
24%
Credit Sales Collected
in following month***based on total sales revenue for the month
49%
Sales Collected in Second month following sale
26%
Total Cash receipts
July
August
September
Quarter
Uncollectible accounts
1%
#2
Lucy’s Gourmet Basket Inc.
Merchandise purchase budget
For the quarter ended September 30, 20XX
July
August
September
Quarter
Sales in units
Desired Ending inventory
Total needs
Less: Beginning Inventory
Units to purchase
Purchase price
Total cost of merchandise purchases
#3
Lucy’s Gourmet Basket Inc.
Selling, General,
and administrative Expense Budget
For the quarter ended September 30,20XX
July
August
September
Quarter
Unit Level expenses
Sales commissions
Customer-Level Expenses
Sales Personnel Salaries
Facility and general operations level expenses:
Media Advertising
Facility Rent
Insurance related to operations
Total facility and general operations-level expenses
Total
expenses
#4
Lucy’s Gourmet Basket Inc.
Cash Disbursements Budget
For the quarter ended September 30,20XX
July
August
September
Quarter
Cost of Merchandise Purchases(from Schedule 2)
Cash payments for purchases paid in month
47%
paid in month after purchase
53%
Total Cash payments for merchandise
Other cash disbursements
Cash payment for selling, general,
and admin. Expenses
Cash Payment for Equipment
Total cash Disbursements
#5
Lucy’s Gourmet Basket Inc.
Cash Budget
For the quarter ended September 30, 20XX
July
August
September
Quarter
Beginning cash
See Schedule 1 above
Cash Available
See Schedule 4 above
Ending cash
#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
Lucy’s Gourmet Basket Inc.
Budgeted Income Statement
For the quarter ended September 30, 20XX
Sales Revenue
Less:
Cost of goods sold
924,000
Gross margin
Other Expenses:
Selling, general, and admin. Expenses
Uncollectible accounts expense
Total other expenses
Net income
#8
What do you think about the survivability of this business?
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.
Did not show on separate
lines, since all SGA
expenses paid in month
incurred.
See schedule 3
Add:
Cash Receipts
Less:
Cash Disbursements
balance of the next month.
Quarter beginning cash
should be the beginning of
the quarter and ending is
end of quarter.
#9
What if the company finds out the monthly rent will increase to $3,000,
what budgets are affected? Why?
Guidance:
I have gone ahead and referenced this cel  
for you, so that you have some of the income 
statement already done for you.

Leave a Comment