Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Data Scenario: You have just been hired into a management position which requires the application of your budgeting skills. You find out that budgeting has

Data Scenario:

You have just been hired into a management position which requires the application of your budgeting skills. You find out that budgeting has not been a priority of the company and that they have been experiencing cash shortages. You have contacted various areas on the organization and have accumulated the information below to assist you in preparing a comprehensive budget.

The following is actual information that relates to the operations of a merchandiser named Sled Company,a wholesaler of sleds as of March 31.

Cash $1,000

Accounts receivable13,680

Inventory 10,757

Accounts Payable$15,781

Actual and Budgeted sales dollar Data-Sales Budget:

March (actual)$38,000

April$36,000

May$37,000

June$39,000

July$35,000

Sales are the following type:64%Cash sales collected in month of sale

36% Credit sales collected in the following month of sale

Credit sales are collected in the month following sale.The accounts receivable at March 31 are a result of March credit sales.

Cost of goods sold equals83%of sales price

At the end of each month, inventory is to be on hand(ending inventory) equal to36%of following month's sales needs, stated at cost.

Inventory purchases are paid49%in month of purchase51%in month after purchase. The accounts payable at March 31 is a result of March purchases of inventory.I have set up a calculation on the budget worksheet to show you this calculation.

Monthly selling expenses are as follows and are paid in the month incurred if it is a cash expense.

Salaries and wages 8%of sales dollar

Commissions 3% of sales dollar

Advertising $900 per month

Utilities $200per month

Monthly general and administrative expenses are as follows and are paid in the month incurred if it is a cash expense.

Rent $2,000 per month

Depreciation$500 for month

Required:You must use cell references on the BudgetSolution worksheet, by referencing this worksheet that contains the data.

Prepare the following second quarter budgets and answer the questions listed on the template provided on the BudgetSolution Worksheet.I have adapted the budget model to meet the needs of this company.

If I bolded a line item, that is a header and does not need computation on that row.

Please note the quarter column is for the quarter so not all lines should be added across in the quarter column.When you have beginning and ending inventory or cash balances this is for the quarter and should be brought over to the quarter column.

1. Prepare Sales Budget in dollars by month and quarter.

2.Prepare merchandise purchase budget by month and quarter in dollars.

3.Prepare SellingExpense Budget, by month and quarter.

4.Prepare General and Administrative budget, by month and quarter.

5.Prepare cash budget showing the months and quarter.

6.Based on the quarterly cash budget prepared, do you have any recommendations on cash management. Discuss the type of business and the cash flow problems a company in this industry might have.

Answer on the budget worksheet , where I have set out the question.

7.Prepare budgeted income statement for the quarter ending June 30, 20XX. You do not need to show monthly columns.I entered Sales for you on the income statement

8.What do you think about the survivability of this business?

9.What if the company finds out the monthly rent will increase to $2,500, what budgets are effected? Why? What is the New Net income(Loss) for the quarter?

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.Please change the rent back to the original amount of 2,000 before you submit.

#1

Sled Company

Sales Budget for the quarter ending June 30, 20XX

April May June Quarter

Sales

#2

Sled Company

PurchaseBudget

For the quarter ended June 30, 20XX

April May June Quarter

Budgeted Sales

Current Cost of goods sold

Desired Ending inventory

Total needs

Less: Beginning Inventory

Total purchase cost

#3Sled Company

Selling Expense Budget

For the quarter ended June 30,20XX

April May June Quarter

Budgeted Sales

Variable selling expenses

Salaries and wages

Commissions

Total variable cash expenses

Fixed Selling expenses:

Advertising

Utilities

Total fixed expenses

Total selling expenses

#4Sled Company

General and Administrative Expense Budget

For the quarter ended June 30,20XX

April May June Quarter

General and Administrative Expenses:

Rent

Depreciation

Total general and administrative expenses

#5

Sled Company

Cash Budget

For the quarter ended June 30, 20XX

April May June Quarter

Beginning cash

Add Cash Collections of Sales:

Cash sales collected in month sale

Credit sales Collected in following month

Total Cash receipts from collection of sales

Cash Available for operations

Less Cash Disbursements:

Cash payments for purchases paid in month

Cash payments made in month after purchase

Total Cash payments for merchandise purchased

Cash Variable Selling Expenses

Cash General and administrative expenses

Total cash Disbursements

Ending cash

6.Based on the quarterly cash budget you prepared, do you have any recommendations on cash management. Discuss the type of business and the cash flow problems a company in this industry might have.

#7

Sled Company

Budgeted Income Statement

For the quarter ended June30, 20XX

Sales

Cost of goods sold

Gross profit

Other Expenses

Selling Expenses

General and administrative expenses

Income(Loss) from operations

8.What do you think about the survivability of this business?

9.What if the company finds out the monthly rent will increase to $2,500, what budgets are effected? Why?What is the New Operating income(Loss) for the quarter?

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.Please change the rent back to the original amount of 2,000 before you submit.

Step by Step Solution

There are 3 Steps involved in it

Step: 1

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

Accounting Information For Business Decisions

Authors: Loren A Nikolai, Billie Cunningham, John D Bazley

3rd Edition

1111066884, 9781111066888

More Books

Students also viewed these Accounting questions

Question

1. Information that is currently accessible (recognition).

Answered: 1 week ago