budget 496317

Aug 29, 2021 | Uncategorized

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. 1
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. 2
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-.75 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.
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 at $33 per unit
Bird Feeder 25,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.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 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 $15
Assembly Department 0.3 hour at $12
Bird Feeder
Fabrication Department 0.45 hour at $15
Assembly Department 0.35 hour at $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%