Budget Scenario Please use excel file to fill out answers with cell references.

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
receivable 13,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 equals 83% of sales price

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

Inventory
purchases are paid 49% in month of purchase
51% 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 $200 per 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

Using the data above answer questions on the attached excel file titled "project" :
Objectives: 1. Develop operating budgets 2. Analyze the cash flow of the company. 3. Analyze how changes in cost or revenue impact the budgets. 4. Provide recommendations to management on cash management. You need to use cell references in the development of your budgets on the BudgetSolution work If you type in any numbers in the solution, I will take off 1 pt. for each typed number, since we us You should use this worksheet as your data field and only use cell references and formulas in your bud Your grade will be based on accuracy of your solution and correct usage of excel. The budget workshe The beauty behind excel is that managers can perform what-if analysis just by changing the data, so yo have used cell references and formulas throughout. BudgetSolution worksheet using information on this worksheet to reference. number, since we use Excel so that we can update budgets or do what if analysis without retyping numbe formulas in your budgets. The budget worksheet has formatted budgets for you to complete. anging the data, so you do not need to retype the budgets if you s without retyping numbers. Budget Scenario I have a template set up on the Budgetsolution worksheet that you should use to complete the required bu You need to use cell references in the development of your budgets on the BudgetSolution worksh If you type in any numbers in the solution, I will take off 1 pt. for each typed number, since we use You should use this worksheet as your data field and only use cell references and formulas in your budge Your grade will be based on accuracy of your solution and correct usage of excel. The budget worksheet The beauty behind excel is that managers can perform what-if analysis just by changing the data, so you have used cell references and formulas throughout. Data Scenario: You have just been hired into a management position which requires the application of your budgeting ski You find out that budgeting has not been a priority of the company and that they have been experiencing You have contacted various areas on the organization and have accumulated the information below to as in preparing a comprehensive budget. The following is actual information that relates to the operations of a merchandiser named Sled Compa Cash $1,000 Accounts receivable 13,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 sa Credit sales are collected in the month following sale. The accounts receivable at March 31 are a result o Cost of goods sold equals 83% of sales price At the end of each month, inventory is to be on hand(ending inventory) equal to 36% of following month's sales needs, stated at cost. Inventory purchases are paid 49% in month of purchase 51% in month after purchase The accounts payable at March 31 is a result of March purchases of inventory. I have set up a calculation Monthly selling expenses are as follows and are paid in the month incurred if it is a cash expense Salaries and wages Commissions Advertising Utilities 8% 3% $900 $200 of sales dollar of sales dollar per month per month Monthly general and administrative expenses are as follows and are paid in the month incurred Rent $2,000 per month Depreciation $500 for month Required: You must use cell references on the BudgetSolution worksheet, by referencing this worksheet Prepare the following second quarter budgets and answer the questions listed on the template provided o 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 co 1. Prepare Sales Budget in dollars by month and quarter. 2. Prepare a merchandise purchase budget by month and quarter in dollars. 3. Prepare a Selling Expense Budget, by month and quarter. 4. Prepare a General and Administrative budget, by month and quarter. 5. Prepare a cash budget showing the months and quarter. 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 industr Type your answer on the budget worksheet , where I have set out the question. 7. Prepare a 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 inc 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? W 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 origin mplete the required budgets and requirements stated below. udgetSolution worksheet using information on this worksheet to reference. umber, since we use Excel so that we can update budgets or do what if analysis without retyping numbers. ormulas in your budgets. he budget worksheet has formatted budgets for you to complete. ging the data, so you do not need to retype the budgets if you n of your budgeting skills. ve been experiencing cash shortages. formation below to assist you r named Sled Company, a wholesaler of sleds as of March 31. nth of sale e following month of sale March 31 are a result of March credit sales. needs, stated at cost. ve set up a calculation on the budget worksheet to show you this calculation. cash expense. nth incurred if it is a cash expense. encing this worksheet that contains the data. he template provided on the BudgetSolution Worksheet. I have adapted the budget model to meet the needs of this c cross in the quarter column. When you have beginning and ending inventory or cash balances this is for the quarter ompany in this industry might have. les for you on the income statement dgets are effected? Why? rent back to the original amount of 2,000 before you submit. without retyping numbers. del to meet the needs of this company. alances this is for the quarter and should be brought over to the quarter column. #1 Sled Company Sales Budget for the quarter ending June 30, 20XX April May June Quarter Sales #2 Sled Company Purchase budget For the quarter ended June 30, 20XX April May June Quarter Sled Company Selling Expense Budget 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 #3 Budgeted Sales Variable selling expenses: Salaries and wages Commissions Total variable cash expenses Fixed Selling expenses: Advertising Utilities Total fixed expenses Total selling expenses #4 Sled 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 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 June 30, 20XX Sales Cost of goods sold Gross profit Other Expenses Selling Expenses General and administrative expenses Income(Loss) from operations 112,000 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

Question

Budget Scenario Please use excel file to fill out answers with cell references.

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

receivable 13,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 equals 83% of sales price

At the end of each month, inventory is to be on hand(ending inventory) equal to

36% of following month's sales needs, stated at cost.

Inventory

purchases are paid 49% in month of purchase

51% 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 $200 per 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

Using the data above answer questions on the attached excel file titled "project" :

Objectives: 1. Develop operating budgets 2. Analyze the cash flow of the company. 3. Analyze how changes in cost or revenue impact the budgets. 4. Provide recommendations to management on cash management. You need to use cell references in the development of your budgets on the BudgetSolution work If you type in any numbers in the solution, I will take off 1 pt. for each typed number, since we us You should use this worksheet as your data field and only use cell references and formulas in your bud Your grade will be based on accuracy of your solution and correct usage of excel. The budget workshe The beauty behind excel is that managers can perform what-if analysis just by changing the data, so yo have used cell references and formulas throughout. BudgetSolution worksheet using information on this worksheet to reference. number, since we use Excel so that we can update budgets or do what if analysis without retyping numbe formulas in your budgets. The budget worksheet has formatted budgets for you to complete. anging the data, so you do not need to retype the budgets if you s without retyping numbers. Budget Scenario I have a template set up on the Budgetsolution worksheet that you should use to complete the required bu You need to use cell references in the development of your budgets on the BudgetSolution worksh If you type in any numbers in the solution, I will take off 1 pt. for each typed number, since we use You should use this worksheet as your data field and only use cell references and formulas in your budge Your grade will be based on accuracy of your solution and correct usage of excel. The budget worksheet The beauty behind excel is that managers can perform what-if analysis just by changing the data, so you have used cell references and formulas throughout. Data Scenario: You have just been hired into a management position which requires the application of your budgeting ski You find out that budgeting has not been a priority of the company and that they have been experiencing You have contacted various areas on the organization and have accumulated the information below to as in preparing a comprehensive budget. The following is actual information that relates to the operations of a merchandiser named Sled Compa Cash $1,000 Accounts receivable 13,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 sa Credit sales are collected in the month following sale. The accounts receivable at March 31 are a result o Cost of goods sold equals 83% of sales price At the end of each month, inventory is to be on hand(ending inventory) equal to 36% of following month's sales needs, stated at cost. Inventory purchases are paid 49% in month of purchase 51% in month after purchase The accounts payable at March 31 is a result of March purchases of inventory. I have set up a calculation Monthly selling expenses are as follows and are paid in the month incurred if it is a cash expense Salaries and wages Commissions Advertising Utilities 8% 3% $900 $200 of sales dollar of sales dollar per month per month Monthly general and administrative expenses are as follows and are paid in the month incurred Rent $2,000 per month Depreciation $500 for month Required: You must use cell references on the BudgetSolution worksheet, by referencing this worksheet Prepare the following second quarter budgets and answer the questions listed on the template provided o 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 co 1. Prepare Sales Budget in dollars by month and quarter. 2. Prepare a merchandise purchase budget by month and quarter in dollars. 3. Prepare a Selling Expense Budget, by month and quarter. 4. Prepare a General and Administrative budget, by month and quarter. 5. Prepare a cash budget showing the months and quarter. 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 industr Type your answer on the budget worksheet , where I have set out the question. 7. Prepare a 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 inc 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? W 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 origin mplete the required budgets and requirements stated below. udgetSolution worksheet using information on this worksheet to reference. umber, since we use Excel so that we can update budgets or do what if analysis without retyping numbers. ormulas in your budgets. he budget worksheet has formatted budgets for you to complete. ging the data, so you do not need to retype the budgets if you n of your budgeting skills. ve been experiencing cash shortages. formation below to assist you r named Sled Company, a wholesaler of sleds as of March 31. nth of sale e following month of sale March 31 are a result of March credit sales. needs, stated at cost. ve set up a calculation on the budget worksheet to show you this calculation. cash expense. nth incurred if it is a cash expense. encing this worksheet that contains the data. he template provided on the BudgetSolution Worksheet. I have adapted the budget model to meet the needs of this c cross in the quarter column. When you have beginning and ending inventory or cash balances this is for the quarter ompany in this industry might have. les for you on the income statement dgets are effected? Why? rent back to the original amount of 2,000 before you submit. without retyping numbers. del to meet the needs of this company. alances this is for the quarter and should be brought over to the quarter column. #1 Sled Company Sales Budget for the quarter ending June 30, 20XX April May June Quarter Sales #2 Sled Company Purchase budget For the quarter ended June 30, 20XX April May June Quarter Sled Company Selling Expense Budget 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 #3 Budgeted Sales Variable selling expenses: Salaries and wages Commissions Total variable cash expenses Fixed Selling expenses: Advertising Utilities Total fixed expenses Total selling expenses #4 Sled 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 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 June 30, 20XX Sales Cost of goods sold Gross profit Other Expenses Selling Expenses General and administrative expenses Income(Loss) from operations 112,000 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

Accepted Answer

The Answer is in the image, click to view ...

Question

Budget Scenario Please use excel file to fill out answers with cell references. Data Scenario: You have just been hired into a management position which

Step by Step Solution

Step: 1

Get Instant Access to Expert-Tailored Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

Management And Cost Accounting

Students also viewed these Accounting questions

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question