Budget

Project 4 is due by Tuesday, October 22nd. Send via the assignment area and make sure you save your file with first initial of first name and last name.

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

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.

Grading Rubric for Project 4

Possible points

1. Prepare a sales budget for November

0.5

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

0.5

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

10. Article answers-.75 each

Total

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.

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

29,000

units

$33

per unit

Bird Feeder

25,000

units

$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.75

pounds per unit of product

Estimated cost of direct materials

Wood

$6.00

per feet

Plastic

$0.90

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

$13

per unit

Bird Feeder

2,500

units

$14

per unit

Estimated inventories at November 30th(ending)

Bird House

5,000

units

$13

per unit

Bird Feeder

2,000

units

$14

per unit

Direct Labor Requirements:

Bird House

Fabrication Department

0.25

hour

$15

Assembly Department

0.3

hour

$12

Bird Feeder

Fabrication Department

0.45

hour

$15

Assembly Department

0.35

hour

$12

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

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.
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 $12,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: Armato, Neil (October 2013). Forward Roll: How Companies Can Move Beyond Traditional Budgeting.
http://www.journalofaccountancy.com/Issues/2013/Oct/20137811.htm
Answer the following questions:
a. How has a rolling forecast helped the company referenced in the article?
b. What are the 3 stages a company needs to convert to a rolling forecast?
c. What are the 4 ways to embrace rolling forecasts?
d. How many days can a company save in their budgeting process based on research by the American Productivity & Quality Center?

30%

13 years ago

Report issue

Budget - Project 4_Bird House Unlimited, Inc._Budgets

NOT RATED

Purchase the answer to view it

project_4_bird_house_unlimited_inc._budgets.xlsx