Two files are attached. One is in microsoft word, and the other is excel. This is a project. The details of how to do this project is in the word document. According to the word document, requirement

Two files are attached. One is in microsoft word, and the other is excel. This is a project. The details of how to do this project is in the word document. According to the word document, requirement 1 and 3 are done in excel. And requirement 2 are done in word document. Thank You

Attachment 1

Attachment 2

ATTACHMENT PREVIEW

Download attachment

Jaguar Corporation
Income Statement
For the Year Ended December 31, 2012
Using Sales Agents
Using Own Sales Force
Revenues
$26,000,000
$26,000,000
Cost of goods sold
Variable
$11,700,000
$11,700,000
Fixed
2,870,000
14,570,000
2,870,000
14,570,000
Gross Margin
11,430,000
11,430,000
Marketing Costs
Commissions
$
4,680,000
$
2,600,000
Fixed Costs
3,420,000
8,100,000
5,500,000
8,100,000
Operating Income
$
3,330,000
$
3,330,000

ATTACHMENT PREVIEW

Download attachment

ACC 381
Summer 2013
Excel Project #1
Data for this exercise is contained in an Excel worksheet. Use Excel for ALL of your
calculations. Do not change the original data; if you are instructed to change information, copy
the original data into another worksheet to complete that requirement. The syllabus has more
detailed instructions for completing the Excel projects.
DO NOT MANUALLY INPUT DATA UNLESS INSTRUCTED TO DO SO! Formulas and cell
references MUST be used; points will be deducted if they are not.
This is NOT a group project! You are expected to complete the assignments on your own!
Completed assignments are to be submitted in the appropriate drop box in Sakai.
INSTRUCTIONS
The Jaguar Corporation manufactures pharmaceutical products that are sold through a network of
external sales agents. The agents are paid a commission of 18% of revenues. Jaguar is
considering replacing the sales agents with its own salespeople, who would be paid a
commission of 10% of revenues and total salaries of $2,080,000. The income statement for the
year ending December 31, 2012, under the two scenarios is shown here:
Jaguar Corporation
Income Statement
For the Year Ended December 31, 2012
Using Sales Agents
Using Own Sales Force
Revenues
$26,000,000
$26,000,000
Cost of goods
sold
Variable
$11,700,000
$11,700,000
Fixed
2,870,000
14,570,000
2,870,000
14,570,000
Gross Margin
11,430,000
11,430,000
Marketing Costs
Commissions
$
4,680,000
$
2,600,000
Fixed Costs
3,420,000
8,100,000
5,500,000
8,100,000
Operating
Income
$
3,330,000
$
3,330,000
1

Unlock Solution Unlocking…

Requirement #1:
In Excel:
[a] Prepare the 2012 income statement using the contribution margin format. [Prepare it in
correct form, i.e., using the heading, etc.]
[b] Calculate Jaguar’s 2012 contribution margin percentage, breakeven revenues, and degree of
operating leverage under the two scenarios.
Requirement #2:
In a Word document:
Using a memo format from you, the CFO of Jaguar Corporation, to the CFO, Stan Martin,
describe the advantages and disadvantages of each type of sales alternative. Based on your
knowledge and the information that you have prepared, make a recommendation to Mr. Martin to
implement one of the alternatives.
Requirement #3:
In Excel:
Assume that in 2013, Jaguar uses its own salespeople, who demand a 15% commission. If all
other cost behavior patterns are unchanged, how much revenue must the salespeople generate in
order to earn the same operating income as in 2012?
2

Leave a Comment