Modeling, Sensitivity Analysis & Financial Excel Functions

Part1:

Sara sells cakes from home. The total fixed cost for every month is budgeted at 200 BD. The labor cost per unit is equal to 2 BD. Sara sells 150 cake every month. The selling prices is 15 BD. The ingredient cost is 4 BD per cake.

Sara wants to know what price she must charge to generate enough revenue to cover her costs. With Break-Even Analysis, Sara can compare different pricing options and calculate how many units sold will lead to profitability. She needs to calculate the contribution margin which equal to selling price per unit minus the variable costs per unit. Contribution margin shows the revenue earned per unit, after deducting variable costs and needs to be enough to cover the company's fixed costs. Sara needs to calculate the following:

1) Break-Even Price, to determine the price needs to be set to generate enough revenue to cover her costs. Break-Even Price equal to 1 / ((1 - Variable Costs Percent per Unit) * (Total Fixed Costs per Unit)). Where Variable Costs Percent per Unit = Total Variable Costs / (Total Variable Costs + Total Fixed Costs). Then determine how changes in unit sold and cost per unit affect Break-Even Price, unit sold between 100 and 200 in 10 increments and Cost per unit between 3.5 and 6.5 in 0.5 increments.

2) Break-Even Units Sold, to determine the number of units that need to be sold to achieve the break-even point. To calculate the Break-Even Units Sold, we divide the total fixed costs by the contribution margin for each unit sold. Then determine how changes in price and cost per unit affect Break-Even Unit, price between 7 and 17 in 1 increments and Cost per unit between 3.5 and 6.5 in 0.5 increments.

Question:

1) Carry through the three stages of logical modeling and drive a spreadsheet model to calculate Break-Even Price and Break-Even Units Sold.

2) Create two data tables showing Break-Even Price and Break-Even Units Sold results.

3) Classify the modeling factors / variables as Input factor, calculated variable, and intermediate variable.

4) Identify the managerial variables Decision variables, output variables. 5) Identify which of the formulas are linear and which are non- linear.

Need urgent help with this, please!!!!

TIA