Project 4 solution

Project 4

You will be graded on the accuracy of your answer and usage of cell referencing in the DATA area.

Covers material in chapter 9.

This project covers material in chapter 9 and I have 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 along with a what if question and article questions.

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 budget director for Bird House Unlimited, Inc., has gathered the following data for use in developing the budgeted income statement for November 20XX.

Estimated sales for November

Bird House        28.600    units    at    $33     per unit

Bird Feeder        24.000    units    at    $37     per unit

Direct materials

Estimated inventories at November 1(beginning)

Wood        2600    feet

Plastic        3200    pounds

Estimated inventories at November 30th(ending)

Wood        3500    feet

Plastic        2800    pounds

Direct materials used in production:

In the manufacture of a Bird House:

Wood        0.80    feet per unit of product

Plastic        0.50    pounds per unit of product

In the manufacture of a Bird Feeder:

Wood        1.20    feet per unit of product

Plastic        0.70    pounds per unit of product

Estimated cost of direct materials

Wood        $6.00     per feet

Plastic        $0.80     per pound

Work in process-given in total of the 2 products

Estimated inventories at November 1(beginning)        $2.000

Estimated inventories at November 30th(ending)        $3.000

Finished Goods

Estimated inventories at November 1(beginning)

Bird House        4.000    units    at    $13     per unit

Bird Feeder        2.500    units    at    $14     per unit

Estimated inventories at November 30th(ending)

Bird House        5.000    units    at    $13     per unit

Bird Feeder        2.000    units    at    $14     per unit

Direct Labor Requirements:

Bird House

Fabrication Department        0.25    hour    at    $14     per hour

Assembly Department        0.3    hour    at    $11     per hour

Bird Feeder

Fabrication Department        0.45    hour    at    $14     per hour

Assembly Department        0.35    hour    at    $11     per hour

Estimated Manufacturing Overhead Costs for November

Indirect Factory Wages        $2.000

Depreciation of Plant and Equipment        1.000

Power and Light        4.000

Insurance and property tax        3.000

Estimated operating expenses for November

Sales salaries expense        $2.000

Advertising expense        10.000

Office salaries expense        5.000

Depreciation expense-office equipment        4.000

Telephone expense-selling        5.000

Telephone expense-administrative        2.000

Travel expense-selling        2.000

Office supplies expense        3.000

Miscellaneous Administrative expense        6.000

Estimated other income and expense for November

Interest Revenue        $14.000

Interest Expense        10.000

Estimated tax rate        30%

Grading Rubric for Project 4

Possible points                Project 4 Objectives:

1.  Prepare a sales budget for November    0.5                1.  Develop operating budgets

2.  Prepare a production budget for November.    1                2.  Analyze the cash flow of the company.

3. Prepare a supporting schedule to determine required material for production and a direct materials purchase budget for November    0.5                3.  Analyze how changes in cost or revenue impact the budgets.

4.  Prepare a direct labor cost budget for November.    2                4.  Provide recommendations to management.

5.  Prepare a factory overhead cost budget for November    2

6.  Prepare a cost of goods sold budget for November.    2

7.  Prepare a selling and administrative budget for November, set up based on categorization of expenses into selling and administrative costs.    1

8.  Prepare a budgeted income statement for November.    2

9.  What if the  company decides to start a new advertising campaign    1

10.  Article answers-.5 each    3

Total    15

If you type in any numbers in the solution, I will take off 5 pts., since we use Excel so that we can update budgets or do what if analysis without retyping numbers.

Name:  Type you name here

Required:  Prepare the following budgets using cell references and formulas in the shaded cells below.  You

should not have any numbers typed into the budgets below, since you will be performing  what if analysis.

I know that my budget format for November is  different than the textbook, but conceptually it is the same.

I have started the sales budget to get you started and to help with understanding cell references to another worksheet.

1.  Prepare a sales budget for November.

2.  Prepare a production budget for November.

3. Prepare a supporting schedule to determine required material for production and a direct materials purchase budget for November.

4.  Prepare a direct labor cost budget for November.

5.  Prepare a factory overhead cost budget for November.

6.  Prepare a cost of goods sold budget for November.

7.  Prepare a selling and administrative budget for November, set up based on categorization of expenses into selling and administrative costs.

8.  Prepare a budgeted income statement for November.

9.  What if the  company decides to start a new advertising campaign

that will increase the total advertising expense to  a total of $15,000 and the estimated sales

of bird house would increase to 30,000 units and Bird Feeders would increase to 26,000 units.

Make these changes on the data sheet and if you have linked your data correctly to these budgets the

budgets should update immediately.  What budgets changed?  What would be the new budgeted income?

Would you recommend this advertising campaign?  Why?

10.  Read the following article:  Wolf, R. (2015). BROKEN BUDGETS? Strategic Finance, 96(12), 22-29. Retrieved from

http://sfmagazine.com/post-entry/broken-budgets-2/

Answer the following questions based on the article cited above:

a.  What is the role of budgeting?

b.  What is the role of forecasting?

c.  What do successful companies set the foundation for an effective planning process begin with?

d.  What should be included in an effective planning blueprint?

e.  What hampers the execution of a business strategy?

f.   What 2 planning alternatives are some companies changing to?

Enter the appropriate cell references or formulas in the shaded cells below.

1.

Bird House Unlimited, Inc.

Sales Budget

For the Month Ending November 30, 20XX

Unit Sales    Unit Selling

Volume    Price    Total Sales

Bird House            28.600     $33.00     $943.800

Bird Feeder

Total revenue from sales

2.

Bird House Unlimited, Inc.

Production Budget

For the Month Ending November 30, 20XX

Units

Bird    Bird

House    Feeder

Expected units to be sold

Plus desired inventory, November 30, 20XX

Total

Less estimated inventory, November 1, 20XX

Total units to be produced

3.

Bird House Unlimited, Inc.

Schedule to determine the required material for production

For the Month Ending November 30, 20XX

Bird House    Bird Feeder

Required units for production:

Wood required per unit(feet)

Wood required for production(feet)

Required units for production:

Plastic required per unit(Pounds)

Plastic required for production(pounds)

Bird House Unlimited, Inc.

Direct Materials Purchases Budget

For the Month Ending November 30, 20XX

Wood    Plastic    Total

Required units for production:

Bird House

Bird Feeder

Plus desired ending inventory, November 30, 20XX

Total

Less estimated beginning inventory, November 1, 20XX

Total units to be purchased

Cost per material

Total direct materials to be purchased

4.        Bird House Unlimited, Inc.

Direct Labor Cost Budget

For the Month Ending November 30, 20XX

Fabrication    Assembly

Department    Department    Total

Hours required for production:

Bird House

Bird Feeder

Total

Hourly rate

Total direct labor cost

5.

Bird House Unlimited, Inc.

Manufacturing Overhead Cost Budget

For the Month Ending November 30, 20XX

Indirect factory wages

Depreciation of plant and equipment

Power and light

Insurance and property tax

Total

6.    Hint:  Do not forget you have 2 products and 2 direct materials, so you will need to combine this information below.

Bird House Unlimited, Inc.

Cost of Goods Sold Budget

For the Month Ending November 30, 20XX

Direct materials:

Direct materials inventory, November 1, 20XX

Direct materials purchases

Cost of direct materials available for use

Less: Direct materials inventory, November 30, 20XX

Cost of direct materials placed in production

Direct labor

Manufacturing overhead

Total manufacturing costs

Work in process inventory, November 1, 20XX

Subtotal

Less work in process inventory, November 30, 20XX

Cost of goods manufactured

Finished goods inventory, November 1, 20XX

Cost of finished goods available for sale

Less finished goods inventory, November 30, 20XX

Cost of goods sold

7.

Bird House Unlimited, Inc.

Selling and Administrative Expenses Budget

For the Month Ending November 30, 20XX

Selling expenses:

Sales salaries expense

Advertising expense

Telephone expense – selling

Travel expense – selling

Total selling expenses

Administrative expenses:

Office salaries expense

Depreciation expense – office equipment

Telephone expense – administrative

Office supplies expense

Miscellaneous administrative expense

Total administrative expenses

Total operating expenses

8.

Bird House Unlimited, Inc.

Budgeted Income Statement

For the Month Ending November 30, 20XX

Sales Revenue

Less:  Cost of goods sold

Gross Margin

Operating expenses:

Selling expenses

Administrative expenses

Total operating expenses

Income from operations

Other income:

Interest revenue

Other expenses:

Interest expense

Income before income tax

Income tax expense

Net income

9.  What if the  company decides to start a new advertising campaign

that will increase the total advertising expense to  a total of $15,000 and the estimated sales

of bird house would increase to 30,000 units and Bird Feeders would increase to 26,000 units.

Make these changes on the data sheet and if you have linked your data correctly to these budgets the

budgets should update immediately.  What budgets changed?  What would be the new budgeted income?

Would you recommend this advertising campaign?  Why?

Please make sure you change your data back to the original information before you submit.

10.  Read the following article:  Wolf, R. (2015). BROKEN BUDGETS? Strategic Finance, 96(12), 22-29. Retrieved from

http://sfmagazine.com/post-entry/broken-budgets-2/

Answer the following questions based on the article cited above:

a.  What is the role of budgeting?

b.  What is the role of forecasting?

c.  What do successful companies set the foundation for an effective planning process begin with?

d.  What should be included in an effective planning blueprint?

e.  What hampers the execution of a business strategy?

f.   What 2 planning alternatives are some companies changing to?

 

You can place an order similar to this with us. You are assured of an authentic custom paper delivered within the given deadline besides our 24/7 customer support all through.

 

[order_calculator]