Accounting Question

Instructions
The purpose of Excel assignments in accounting is to provide students experience
using a spreadsheet to solve short accounting applications related to material
covered in class. Excel Problem #2 requires you to perform simple mathematical
functions (add, subtract, multiply, divide) and graph results.
To begin, please enter 45,000 plus the square root of 17,358 in the yellow cell
indicated (F9). 9). You can use the following formula… =45,000 + sqrt (17,358)
3 POINTS can be earned by correctly determining net operating income in the three
scenarios. NOTE: when preparing the 3 income statements, the starting point is the
original information provided in the Base Scenario (i.e. cells F6 through F10) – do
NOT use the adjusted income statement in Scenario 1 as a starting point for
Scenario 2.
2 POINTS are for completing the spreadsheet using formulas, not numbers, in all
gray cells.
5 POINTS are for correctly filling out the Cost-Volume Profit Graph Data Table in
cells K9:N12
5 POINTS can be earned by correctly graphing the CVP graph. Fill out the “CostVolume-Profit Graph Data Points” table by calculating the profit for the base
scenario and each other three scenarios at the four different given levels of units
sold. Use a “XY (Scatter) with Straight Lines” graph on this data. Format the chart of
have Unit Sales on the X-axis and Profit on the Y-axis begins at zero units.
ACCT
Base Scenario Current Model
Number of Units sold
EXCEL PROBLEM 2
10,000
Sales
Variable Expenses
Contribution Margin
Fixed Expenses
Net Operating Income
Scenario #1
Scenario #2
Scenario #3
Invest in Machine with
$60,000 fixed charge;
Reduce Variable Cost per
unit by $9 per unit;
Number of Units Sold
Reduce Variable Cost $3
per Unit; Keep Sales at
Base Level
Number of Units Sold
10,000
Sales
Variable Expenses
Contribution Margin
Fixed Expenses
Net Operating Income
10,000
Sales
Variable Expenses
Contribution Margin
Fixed Expenses
Net Operating Income
Increase Marketing Budget Number of Units Sold
for TV advertisements by
$15,000; Reduce Sale Price Sales
Per Unit by $1; Get 5,000 Variable Expenses
More Sales
Contribution Margin
Fixed Expenses
Net Operating Income
15,000
SPRING
Insert 45,000 plus the square root 17,358
in the yellow highlighted cell
PER UNIT
TOTAL
$ 350,000 $
35.00
$ 245,000 $
24.50
$ 105,000 $
10.50
$
45,000
$
60,000
TOTAL
Cost-Volume-Profit Graph Data Points
Units Sold
Base
#1
0
1,000
10,000
15,000
PER UNIT
Insert your graph under here.
TOTAL
PER UNIT
TOTAL
PER UNIT
olume-Profit Graph Data Points
#2
h under here.
#3