Project #2 - Budgeting

Project #2 - Budgeting
Learning Objectives:
1) Provide a comprehensive budgeting experience.
2) Exhibit the use of Excel in creating a master budget.
3) Utilize the project to perform sensitivity analysis.

Learning Outcomes:
1) Understanding of the inter-relatedness of budgets.
2) Ability to utilize basic formula, cell references, and formatting.
3) Ability to perform sensitivity analysis and make decisions from that analysis.

Requirements:
1) Fill in the missing data (Selling Price Per Unit) on the Data Sheet.
2) Utilize your textbook, lecture notes, etc. to complete the budgets.
a) You may not type any numbers on the budget worksheets.
b) Utilize cell references to transfer numbers from the data sheet to the
worksheets.
c) Use formula to perform calculations.
3) Ensure that the Income Statement shows a profit for each month. If not,
change the selling price on the Data Sheet until a profit results for each
month.

Excel Help:
http://www.gcflearnfree.org/excel2010

Project #2
Sales Projections in Units
January
February
March
April
May

4,600
16,300
32,000
41,000
56,700

Projected Sales Price/Unit

???

Monthly Projected Selling & Administrative Expenses
Variable Cost/Unit

$1.34

Fixed Costs

$3,450

Production:
Desired Ending Inventory
Beginning Inventory (new business)

4.5%
0

Materials
Desired Ending Inventory

8.0%

Number of Materials per Unit

7.10

Projected Cost/Material Unit

$0.32

Beginning Inventory (new business)
Direct Labor
Time per Unit (in hours)
Cost per Hour

0

0.25
$10.35

Manufacturing Overhead
Variable Cost/Unit
Fixed Costs

$4.89
$12,430

SALES BUDGET
January

February

Projected Sales in Units
Selling Price per Unit
Anticipated Sales Dollars

37

March

April

SELLING & ADMINISTRATIVE (S&A) EXPENSE BUDGET
January
Sales in Units
Variable S&A Rate/Unit
Anticipated Variable Costs
Fixed S&A Costs
Total Anticipated Selling &
Administrative Costs

February

March

PRODUCTION BUDGET
January
Sales in Units
Desired Ending Inventory
Total Units Needed
Beginning Inventory
Total Production in Units

February

March

April

MATERIALS REQUIREMENTS BUDGET
January
Units to be Produced
Material/Unit
Total Production Need
Desired Ending Inventory
Total Materials Needed
Beginning Inventory
Total Materials Needed to be
Purchased
Cost per Unit of Material
Total Material Cost

Material Cost per Unit of Product:

February

March

April

DIRECT LABOR BUDGET
January
Number of Units Produced
Direct Labor Hours/Unit
Total Direct Labor Hours
Needed
Cost/Direct Labor Hour
Total Direct Labor Cost

Direct Labor Cost per Unit of Product

February

March

MANUFACTURING OVERHEAD BUDGET
January

February

March

Production in Units
Variable OH Rate/Unit
Anticipated VOH Costs
Fixed OH Costs
Total Anticipated OH Costs

Predetermined OH Rate
Total Estimated OH Costs
Total Estimated DLHs

MOH per Unit:
Predetermined MOH rate
DLHs per Unit
MOH per Unit

=

per DLH

Product Cost
Direct Materials
Direct Labor
Manufacturing Overhead
Total Unit Cost

PROJECTED INCOME STATEMENT
January
Sales in Units
Sales Revenue
Cost of Goods Sold:
Gross Profit (Margin)
Selling and Administrative
(Operating) Expenses
Operating Income

February

March