FIN-5823 Monthly Budgeting Scenario Sensitivity and Simulation AnalysisMatlab

Java Python 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.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.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 ($.02)

o Inspection ($.10 per unit)

o Maintenance and Repair ($0.03)

o Utilities ($.01 per unit)

●   Fixed Overhead Expenses are $1,100 monthly.

Create a Selling and Administrative Budget

●   Variable Selling & Administrative Expenses are:

o Sales Commissions ($.03 per unit)

o Delivery ($.01 unit)

o Office Support ($.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 projec FIN-5823 Monthly Budgeting, Scenario, Sensitivity, and Simulation AnalysisMatlab t.

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 $.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.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 $.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.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 .5, and the best- and worst-case scenarios as .25 each