This case study is about a small manufacturing company. The concepts covered are flexible budgets. contribution margin, fixed and variable costs, budgeting, profitability, analysis, variances, and business decisions.

Anonymous

Question Description

The case study is worth 50 points. The submission will be one Excel file covering all parts of the Case Study (multiple tabs can be used if desired). ONY EXCEL FILE SHOULD BE SUBMITTED. Any other file types or submissions (Word, etc) will be ignored.

Unformatted Attachment Preview

MBA 6100 Case Study #2 – Static and Flexible Budgets and Budget Variances Create an Excel file to show the budgets and calculations for the questions below. Assume no beginning or ending inventory (they will produce the products in the same month that they sell them). Ignore taxes. Analysis and explanations can be included in the Excel cells or in a textbox. The submission should be one Excel file only. No additional files should be submitted. Use contribution margin income statement formatting. Students are expected to work independently. Note: The 2nd question for this case study is on page 2, so keep scrolling down! LiveColor is preparing their 2019 budget. They estimate sales/production will be between 700,000 and 900,000 boxes of markers per month. LiveColor wants to look at both static budgets and flexible budgets to determine which is best for them. They have struggled in the past with determining whether budget variances were related to volume being above or below budget vs whether they are spending too much or too little on expenses. They want to be able to understand their budget variances in order to make better decisions. Note: Treat Salary and Wages Costs as Fixed Expenses. Question 1: Prepare some budgets in Excel for LiveColor. (20 points) a) Show the static budget based on 800,000 units (boxes) produced. b) Show what the flexible budget would be if 700,000 units (boxes) were produced. c) Show what the flexible budget would be if 900,000 units (boxes) were produced. d) Show the flexible budget cost formula(s) for LiveColor. e) Explain the difference between static and flexible budgets and when each should be used. Question 2: The month of January 2019 is complete, and LiveColor wants to compare their budget to their actual results. Actual results are shown in the table above. (30 points) a) Compare January’s actual results to the static budget you created in Question 1a. Analyze the static budget variances by comparing the static budget to the actual results. b) Break out price and volume variance amounts for each line item. c) For the static budget variances, indicate whether each line item is favorable or unfavorable. Provide possible explanations. d) Create the flexible budget based on the actual units produced for January. e) Compare the actual results to the flexible budget for January that you created in Question 2d. Analyze the flexible budget variances by comparing the flexible budget to the actual results. f) For the flexible budget variances, indicate whether each line item is favorable or unfavorable. Provide possible explanations. g) LiveColor wants to determine whether they should use a flexible budget or a static budget going forward. Write a memo to their CFO explaining some pros and cons of each option. Provide a recommendation including the reason(s) you recommend that approach. ...
Purchase answer to see full attachment

Tutor Answer

veronajune
School: Duke University

Attached.

Static Budget Based on 800,000 units produced
Production Units

Sales
Raw Materials Costs

Packaging Cost
Salary & wages Costs
Overtime (OT)
Fringe Benefit
Electricity
Waste & Other Costs
Rent Costs
Insurance Costs
Depreciation Costs
Total Budgeted costs
Profit

800,000
X
Y=X*800,000
Per Unit Total
$5
$4,000,000.00
$1.50
$1,200,000.00
$0.80
$640,000.00
$300,000.00
$0
$0.00
$150,000.00 (50%of $300,000)
$0.20
$160,000.00
$0.10
$80,000.00
$500,000.00
$60,000.00
$240,000.00
$3,330,000.00

$670,000.00 (4000000-3330000)

Static Budget Based on 700,000 units produced
Production Units

700,000

X
Y=X*700,000
Per Unit Total
Sales
$5 $3,500,000.00
Raw Materials Costs
$1.50 $1,050,000.00
Packaging Cost
$0.80
$560,000.00
Salary & wages Costs
$300,000.00
Overtime (OT)
$0
$0.00
Fringe Benefit
$150,000.00 (50%of $300,000)
Electricity
$0.20
$140,000.00
Waste & Other Costs
$0.10
$70,000.00
Rent Costs
$500,000.00
Insurance Costs
$60,000.00
Depreciation Costs
$240,000.00
Total Budgeted costs
$3,070,000.00

Profit

$430,000.00 (4000000-3330000)

Static Budget Based on 900,000 units produced
Production Units
900,000
X
Y=X*900,000
Per Unit Total
Sales
$5
$4,500,000.00
Raw Materials Co...

flag Report DMCA
Review

Anonymous
The tutor was pretty knowledgeable, efficient and polite. Great service!

Anonymous
Heard about Studypool for a while and finally tried it. Glad I did caus this was really helpful.

Anonymous
Just what I needed… fantastic!

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4
Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors