## (solution) ACCT 509 Budgeting Project-Fall 2016 Instructions: This project

I saw your profile in the accounting section and wonder if you would like to complete this assignment for me. Let me know what you think. I would need it done by Monday night. I would be willing to pay \$100.00. Thanks

ACCT 509 Budgeting Project-Fall 2016

Instructions:

This project is worth 80 points and MUST BE TURNED IN by 11:59 pm CST, Wednesday, October 12,

2016. While you may consult with others on the project, each person must prepare their solution

individually. It is not acceptable to copy and share files or for one person to do the solution and print it

out multiple times. Doing this is a violation of academic dishonesty and will carry stiff consequences.

Prepare a solution for the problem below using ONLY one Excel file. Your spreadsheet MUST be

properly formatted and include a data section (in one worksheet/tab) and a separate solution section (in

another worksheet/tab within the same excel file). The data section MUST contain only the data

presented in the problem. The contents of the cells in the solution section MUST contain ONLY

cell references, formulae, and labels, but NO TYPED-IN NUMBERS. This is how your work will be

shown. All cells within the solution section MUST have a label! Preparing the spreadsheet in

this manner allows it to be easily revised for new data due to changes in assumptions or in

subsequent periods. After completing the project, please submit your electronic file (only one file) via

e-mail to me at Matthew.Notbohm@business.und.edu. Problem:

Mahoney Inc. makes solid steel desks engraved with words, symbols or designs. Although

production occurs throughout the fiscal year, sales are primarily in the months of September

through December.

The Sales Department anticipates a 10% growth in sales in each of the next two years. The

sales in units for the current year (2016) and the anticipated sales for each of the next two

years (2017 and 2018) are given below.

Sales in units

2016

2017

2018

Steel Desks

100,000

110,000

121,000

Each quarter?s sales are expected to follow the pattern below, as a percent of annual unit

sales:

Quarter 1 (Jan. to March)

10%

Quarter 3 (July to Sept.)

30%

Quarter 2 (Apr. to June)

15%

Quarter 4 (Oct. to Dec.)

45%

The selling price per desk is expected to be \$550.

The following data pertain to production policies and manufacturing specifications followed by

Mahoney:

a.

The desired ending finished goods inventory for each quarter is 25 percent of next

quarter?s sales in units. The ending finished goods inventory for the year 2016 followed

this pattern. The cost per unit of finished goods inventory at Dec. 31, 2016, the fiscal

year end, is \$474.56.

b.

The main raw material in these desks is steel. Each manufactured desk needs 40

pounds of steel. The desired ending materials inventory is 30% of next quarter?s

expected production needs, and the same pattern was followed at the end of both 2015

and 2016. The anticipated purchase price for steel over each of the next two years is

\$10 per pound, which is the same price as in 2016. Other materials required in

producing desks are treated as indirect manufacturing costs.

c.

Two types of direct labor are used to make the chairs, craftsman labor for forming and

assembling, and unskilled labor for polishing. The expected labor needs per desk and

hourly pay rates for each type of labor are as follows:

per Desk

Wage Rate

Craftsman

2.5 hours

\$22

Unskilled

0.5 hours

\$13

d.

varies with direct labor hours (DLH).

Indirect materials

\$1.20 Utilities

Maintenance

Supervision

Depreciation

Other

e. \$15,000

\$45,000

\$55,000

\$30,000 \$0.60

\$0.55

\$0.25 Quarterly selling and administrative expenses are estimated as follows. Variable selling

and administrative expenses vary with units sold.

Fixed S &amp; A

Variable S &amp; A (per unit sold)

Salaries

\$100,000

Commissions

\$ 4.00

Depreciation

\$ 15,000

Shipping

\$10.00

Other

\$ 5,000

\$ 3.00 REQUIRED:

a.

Prepare operating budgets for 2017 by quarter and for the year (so you?ll need at

least 5 columns of numeric data for each budget schedule) (Hint: For some of the

following budget schedules you will also need to complete the schedules for some

combination of Q4 of 2016, and Q1 of 2018 and Q2 of 2018, so you?ll do well to just

compute those values whenever you can, as some of this data will be necessary for

later calculations) with the following schedules:

:

1.

Sales revenue budget

2.

Production budget (Note: Beginning and ending inventory for the year

correspond with the beginning inventory for Q1 and ending inventory for Q4.)

3.

Direct materials usage budget

4.

Direct materials purchases budget (Note: Beginning and ending

inventory for the year correspond with the beginning inventory for Q1 and

ending inventory for Q4.)

5.

Direct labor budget

6.

overhead allocation rates for variable and fixed overhead, only compute the rate

per year, not per quarter-FMOH rate is too volatile when computed by quarter.

Note that the rates are per direct labor hour.

7.

8.

Ending inventory budget for Materials Inventory and Finished Goods

Inventory. Assume there is no Work in Process Inventory. (Hint: For finished

goods inventory, you will need to add the input costs per unit (direct materials,

direct labor, overhead) to get the cost per unit of finished goods for each

product.)

9.

Budgeted income statement using the contribution margin format (that

is, costs grouped by cost behavior, variable vs. fixed. See the actual results

given in part b below for an example of the format.)

b. Given below are the actual results at the end of fiscal 2017.

a.

Compare the actual results with the static (master) budget that you

generated in part a.

b.

Revise the budget for the actual volume (i.e. assemble a flexible budget)

and compare the flexible budget with the actual results.

c.

Evaluate the performance of the company for 2017. Which budget,

static or flexible, do you believe provides the better comparison? Why?

d.

a flexible budget for comparing with the actual results in managers? performance

evaluations? When is a static budget variance better? When is a flexible budget variance better?

2017 Actual Results

Units sold

Revenues

Variable cost of goods sold

Direct materials

Direct labor

Variable OH

Commissions

Shipping

Other

Total variable costs

Contribution margin

Maintenance

Supervision

Depreciation

Other

Salaries

Depreciation

Other

Total fixed costs

Operating income 112,500

\$61,875,000

45,225,000

6,975,000

879,500

500,000

1,091500

349,000

55,020,000

\$6,855,000

57,000

173,000

472,000

121,000

410,000

61,000

22,000

1,316,000

\$5,539,000

Solution details:
STATUS
QUALITY
Approved

This question was answered on: Jan 30, 2021

Solution~0001000565.zip (25.37 KB)

This attachment is locked

We have a ready expert answer for this paper which you can use for in-depth understanding, research editing or paraphrasing. You can buy it or order for a fresh, original and plagiarism-free solution (Deadline assured. Flexible pricing. TurnItIn Report provided)

STATUS

QUALITY

Approved

Jan 30, 2021

EXPERT

Tutor