Aldraawi, Hanan Record Your Answers in the Green Cells

2010 Income Statement

Revenue Number 1

Projected Units Sold

Price per Unit 25,000

$40.00 Name

1 Build the formulas for the highlighted cells. (Use the same Formulas in #7)

2 Using Units Sold at the Projected Number Record Net Income or Loss at

Using Units Sold at the Projected Number Record Total Revenue

Using Units Sold at the Projected Number Record Total Expenses 25000

25000

25000 2a

2b

2c 1 Total Revenue Variable Expenses

Units Produced

Variable Cost per Unit 3 Using Goal Seek Calculate the Units Sold at the following:

3a Breakeven Point in Units

0

25,000

$24.00 3a 3b Desired Profit in Units of Projected 3b 200000 1 Total Variable Expenses 4

Fixed Expenses

Salaries and Benefits

Advertising

Administrative

Miscellaneous Projected

$240,000.00

$25,000.00

$15,000.00

$12,000.00

Projected Summary 35,000 Units 15,000

20,000

25,000

30,000

35,000

40,000 1 Total Fixed Expenses What is the Projected Net

Income @ Change the Units Sold back to 25000

and do the following:

Create a One-Variable Data Table with the following parameters.

Units Sold

Revenue

Expenses

Net Income Total Revenue

Total Expenses

1 Net Income 5 Using the One-Variable Data Table above Create:

A Scattergram Chart of Cost Volume Profit Analysis with

Revenue and Expenses = Dependent Variable

Units Sold = Independent Variable

Save the Chart in another Sheet of this Workbook

6 Change the Units Sold back to 25000

and do the following:

Create a Two-Variable Data Table with the following parameters. 35 Net Income Analysis

Price per Unit

40

45 15,000

20,000

25,000

30,000

35,000

40,000

Using Senario Manager Calculate Net Income Using the Data In the Yellow and Record 7

Your Answers in the Green Boxes Below. Projected Revenue Units Sold

Price per Unit 25,000

$46.00 Total Revenue $1,150,000.00 Variable Expenses

Units Produced

Variable Cost per Unit Projected

25,000

$27.60 Total Variable Cost $690,000.00 Total Variable Expenses $690,000.00 Total Fixed Expenses $292,000.00 Projected

$1,150,000.00

$982,000.00 Summary Total Revenue

Total Expenses

Net Income $168,000.00 0.00% Total Fixed Expenses Selling Price/Unit

Best Case

Worst Case

Most Likely Case Net Income at the Listed Cases

Best Case

Worse Case

Most Likely Case 385000

250000

292000 $55.00

$35.00

$46.00 7a

7b

7c Demand Volume 40,000 7a

15,000 7b

25,000 7c What is the Projected Net

Income @

50 55 40,000 Units

45 Price Record Your Answers in the Green Cells

2010 Income Statement

Revenue Starting Units Sold

Units Sold Projected at Max

Forecasted Price per Unit Projected

29,200

21,208

$35.00 Total Revenue Projected

21,208

$15.00 Total Variable Cost 10%

12%

1.2 $318,126.32 Variable Portion Fixed Cost per Unit

Total Variable Portion of Fixed Cost

Total Variable Expenses Fixed Expenses

Salaries and Benefits

Advertising

Administrative

Miscellaneous

Total Fixed Expenses

Summary Total Revenue

Total Expenses

Net Income $0.025

$530.21

$318,656.53 Projected

$240,000.00

$25,000.00

$15,000.00

$12,000.00

$292,000.00 Projected

$742,294.74

$610,656.53

$131,638.21 Original Price Per Unit

Price Elasticity

Price Change Units Sold at Max = Starting Units Sold * (1+Price Elasticity * (1-Price Change) $742,294.74 Variable Expenses

Units Produced

Variable Cost per Unit Parameters

Increase In Price

Decrease In Units Sold

Price Elasticity of Demand Units Produced 0% More than Demand Using Solver Calculate the Price per Unit to

Maximize Net Income subject to the following

Constraints: The Maximum Number of Units that

can be Sold and the Minimum Number of Units

that can be Sold

Maximum Number of Units

35,000

Minimum Number of Units 15,000 28.5

1.20

Forecasted Price Per Unit/Original Price Per Unit Find Production to Maximize Profit

Tables

Variables

Units Produced

Profit Per Unit

Objective Function Chairs

25

70 Constraints Hrs

Carpentry

Painting 4

2 Total Profit 25.00

50 3

1 Given:

Profit Per Unit

Hours required in Each Department

Constraints Hrs

Carpentry

Painting

Maximum Number of Tables Total Hours Constraint

Carpentry

Painting

Using Solver and the given information to find the production of table and chairs to maximize profit. 0% Tables Chairs

70 50 4

2 3

1 Total Hrs 24 240

100