Chapter 10 – Strategy and the Master Budget
10-52 (Continued-4)
Schedule 2: Cost per Unit—Product D57:
Inputs Cost
Cost Element Unit Input Cost Quantity Per Unit
RM-1 $2.00 7 $14.00
RM-2 $2.50 3.6 $9.00
RM-3 $0.50 0.8 $0.40
7. Selling and Administrative Expense Budget
Spring Manufacturing Company
Selling and Administrative Expense Budget
2016
Selling Expenses:
Advertising $60,000
Sales salaries 200,000
Travel and entertainment 60,000
Depreciation 5,000 $325,000
Administrative expenses:
Offices salaries $60,000
10-70
Education.
Chapter 10 – Strategy and the Master Budget
10-52 (Continued-5)
8. Budgeted Income Statement
Spring Manufacturing Company
Budget Income Statement
For the Year 2016
C12 D57 Total
Sales (part 1) $1,800,000 $1,980,000 $3,780,000
Cost of goods sold (part 6) 1,036,700 1,020,500 2,057,200
Gross profit $763,300 $959,500 $1,722,800
Answers:
manufacturing elements reduce wear and tear of equipment and other facilities and
lessens the need for additional capital investments/replacements.
Note to Instructor: An Excel spreadsheet solution file is embedded in this document.
You can open this spreadsheet “object” that follows by doing the following:
1. Right click anywhere in the worksheet area below.
2. Select “Worksheet Object,” then “Open.”
3. To return to the Word document, select “File” and then “Close and return to…”
while you are in the spreadsheet mode.
10-71
Education.
Pr. 10–52 7e.xlsx
Chapter 10 – Strategy and the Master Budget
10-53 Cash-Flow Analysis; Sensitivity Analysis (60 minutes)
1. Estimated Cash Receipts, April 2016:
April Cash Receipts:
April cash sales (30.0% × $425,000) = $127,550
April credit-card sales ($425,000 × 65% × 97%) = 267,963
Collection of accounts receivable:
2. Purchase Order for Hardware, executed January 25th:
a) Number of units to be ordered:
Estimated Unit Sales, March (given) = 90
Plus: Desired Ending Inv., March (30% × 100) = 30
b) Cost of purchases:
Selling price per unit (e.g., $300,000 ÷ 100 units) = $3,000
Note that the cash outflow associated with these purchases will be on
4/10/2016.
10-72
Scenario (units)CGS % April 10th
1 100 55% $165,000
2 100 60% $180,000
3 100 65% $195,000
4 90 55% $153,450
9 80 65%
0
Maximum = $195,000
Minimum = $141,900
Range = $53,100
Algorithm:
= budgeted CGS/unit × [(Estimated sales in March + (0.3 × 100) – (0.3 Estimated Sales,
March)]
which (because April sales are assumed known and equal to 100 units) reduces to:
= budgeted CGS/unit × [((1 – 0.3) × Estimated sales, March) + 30]
10-73
March.
Examples:
10-53 (Continued-2)
When CGS % = 55% and Estimated Sales in March = 100 units:
Estimated Cash Payment on April 10th
= Purchase price/unit × Number of Units Purchased on January 25th
= ($3,000 × 60%) × [(0.7 × 100 units) + 30 units]
= $1,800/unit × 100 units purchased on January 25th = $180,000
10-74
The following web-accessible tutorials regarding the use of Excel 2010 to perform “What-
If analysis” may be helpful:
1. Introduction to What-If Analysis: http://office.microsoft.com/en-us/excel-
help/introduction-to-what-if-analysis-HA010342628.aspx
2. Using Excel to Perform Scenario Analysis: http://office.microsoft.com/en-us/excel-
help/switch-between-various-sets-of-values-by-using-scenarios-HP010072669.aspx
3. Using Excel to Create Data Tables:http://office.microsoft.com/en-us/excel-
help/calculate-multiple-results-by-using-a-data-table-HP010342214.aspx
4. Using the Goal Seek Routine in Excel:http://office.microsoft.com/en-us/excel-
5. Using Solver to Perform What-If Analysis:http://office.microsoft.com/en-us/excel-
help/define-and-solve-a-problem-by-using-solver-HP010342416.aspx
4. Monthly cash budgets are prepared by companies such as CompUSA, Inc., in order
to plan for their cash needs. This means identifying when both excess cash and cash
shortages may occur. A company needs to know when cash shortages will occur so
that prior arrangements can be made with lending institutions in order to have cash
1. Break-even volume, in units and dollars, for the coming year:
Annual fixed costs =
$1,200,00
0
Contribution margin, per unit:
2. Units needed to be sold for the company to meet the $300,000 pre-tax profit goal:
Annual fixed costs (FC) = $1,200,000
3. What-If Analysis
Notes:
1. Revised variable cost/unit = baseline cost/unit + (assumed % change in DL cost
component × labor cost component of variable cost/unit). For example, Situation
1: If there is a 4% increase in the DL cost per unit, the revised variable cost/unit
2. Revised contribution/unit = selling price/unit – revised variable cost/unit. For
example, Situation 1: With a 4% increase in the DL cost/unit, the revised
contribution margin/unit = $29.00 = $100.00 – $71.00/unit.
3. Breakeven volume (units) = Fixed costs ÷ contribution margin/unit. For example,
4. Unit Change in Breakeven Point = Revised Breakeven Point – Original
5. % Change in Breakeven Point = Unit change in Breakeven Point/Baseline
4. Selling price per unit the company must charge to maintain the budgeted ratio of
contribution margin to sales (hint: Use the Goal-Seek function in Excel to answer this
question):
Original selling price per unit = $100.00
Original variable cost per unit = $70.00
Original contribution margin per unit = $30.00
Original contribution margin ratio = 30.00%
Increase in labor-cost component of vc per unit = 5.00% (assumed
)
Labor-cost component of variable cost per unit (given) = $25.00
Revised variable cost per unit ($70.00 + (0.05 × $25.00)) = $71.25
5. As stated in the chapter, inputs to the construction of individual budgets are subject to
uncertainty. That is, the inputs represent forecasts (e.g., selling price per unit, sales