FIN$-5823$

Monthly Budgeting, Scenario, Sensitivity, and Simulation Analysis

This is a two$-$week assignment. You will prepare the monthly budget for a new company during the first week. During the second week you will use scenario, sensitivity$,$ and simulation analysis to better understand the risk of the new business.

Week Three

Create an operating budget for the first quarter of a new business using the FIN$-5823$ Budget Planning Template.

Projected Sales are:

$$ Month #$1$: $$65,000$

$$ Month #$2$: $$97,500$

$$ Month #$3$: $$130,000$

$$ Month #$4$: $$110,500$

These sales are based on selling one product with a selling price of $$6\mathrm{.}50$

Create a Production Budget based on Units $($not dollars$).$

$$ Desired Finished Goods Ending Inventory each month is $10\%$ of the next month$$s projected sales. $($Use the Finished Goods Inventory amount for the previous year for the Beginning Finished Goods Inventory to start this year. The Finished Goods Inventory for the previous month is $1000$ units.$)$

Create a Direct Materials Purchase Budget

$$ Each unit produced requires $10$# $($pounds$)$ of direct materials at a cost of $$0\mathrm{.}08$ per pound.

Create a Direct Labor Budget

$$ Direct Labor Costs are $$15/$ per hour

$$ Each unit requires $5$ minutes of labor

Create an Overhead Budget

$$ Variable Overhead Expenses are:

o Supplies $($$$\mathrm{.}02)$

o Inspection $($$$\mathrm{.}10$ per unit$)$

o Maintenance and Repair $($$$0\mathrm{.}03)$

o Utilities $($$$\mathrm{.}01$ per unit$)$

$$ Fixed Overhead Expenses are $$1,100$ monthly.

Create a Selling and Administrative Budget

$$ Variable Selling & Administrative Expenses are:

o Sales Commissions $($$$\mathrm{.}03$ per unit$)$

o Delivery $($$$\mathrm{.}01$ unit$)$

o Office Support $($$$\mathrm{.}02$ per unit$)$

$$ Fixed Selling & Administrative Expense are $$2,500$ monthly.

Combine all Budgets to Create a Budgeted Income Statement

Complete a Budgeted Income Statements for months $1$ through $3,$ and SUM the three months for a Budgeted Income Statement that reflects the total first quarter.

Week Four

Sensitivity analysis involves assessing the effect of changes in one input variable at a time. These inputs may include sales, fixed costs, and variable costs which all affect the profitability of a project.

Scenario analysis creates different scenarios that can be labeled as $(1)$ pessimistic, $(2)$ optimistic, and $(3)$ most likely scenario. It then changes the input variables and calculations the NPV for each scenario.

Simulation analysis involves examining the effect on NPV all uncertain input variables follow their corresponding probability distributions.

Sensitivity Analysis

How much does the first quarter net income increase if sales are $10\%$ more each month than forecasted?

How much does the first quarter net income decrease if sales are $15\%$ less than forecasted each month?

Using the original sales projections, what is the impact on the first quarter net income if

the desired FG inventory is increased from $10\%$ of the next months projected sales to $25\%.$

What is the impact on the first quarter net income if the cost of labor per hour increased from $$15$ to $$25?$

What is the impact on the first quarter net income if each unit took $10$ minutes instead of $5$ minutes of labor?

Scenario Analysis

Assume the budgeted income statement you prepared during Week Three is the most likely case. Re$-$title the budgeted income statement as Bgt IS Most Likely. Create two more scenarios, the best and worst cases. Label the worst case as Bgt IS Worst, and the best case Bgt IS Best.

The Worst$-$Case assumptions follow:

$$ Sales are $25\%$ lower each month.

$$ Direct materials cost $$\mathrm{.}10$ per pound

$$ It takes $6$ minutes to produce each unit and labor costs $$18$ per hour.

$$ Fixed overhead is $$2,500$ per month.

$$ Fixed selling & administrative costs are $$5,000.$

$$ Sales commissions are $$0\mathrm{.}05$ per unit

What is the first quarter net income for the worst case?

The Best$-$Case assumptions follow:

$$ Sales are $25\%$ higher each month.

$$ Direct materials cost $$\mathrm{.}06$ per pound

$$ It takes $4$ minutes to produce each unit and labor costs $$14$ per hour.

$$ Fixed overhead is $$1,000$ per month.

$$ Fixed selling & administrative costs are $$1,500.$

$$ Sales commissions are $$0\mathrm{.}02$ per unit

What is the first quarter net income for the best case?

Calculate the first quarter net income by weighting the most likely as $\mathrm{.}5,$ and the best$-$ and worst$-$case scenarios as $\mathrm{.}25$ each.