Teaching Basic Marketing Accountability Using Spreadsheets: An Exploratory Perspective Page: 30
9 p.View a full description of this article.
Extracted Text
The following text was automatically extracted from the image on this page using optical character recognition software:
Exhibit 2b: Excel Template for a Typical Case-let
(this is available to students)
A B I C D E F G
1 Texas Bearing Company
2 Illustration of breakeven analysis without and with profit
3 worksheet created by: Dr. Gopala Ganesh, UNT Denton, Texas
4
5 INPUTS SECTION
6
7 Information on Prices and Margins:
8
9 Retail Price of a typical TBC Bearing ?
10 Average retailer % markup on selling price ?
11 Wholesale % distributor markup on cost ?
12
13 Information on Variable Costs :
14
15 Labor and Material Cost of Each Bearing ?
16 Sales Representative Commission %
17
18 Information on Fixed Costs:
19
20 Production Setup
21 Salesperson Salary
22 Salesperson Travel Support ?
23 Cost of Advertising and Sales Promotion ?
24
25 Information : Profit Goal
26
27 Expected Contribution to Margin
28
29 Information : Other
30
31 Number of Field Sales Representatives
32
33 OUTPUTS SECTION
34
35 Q1
36 Retailer's selling price per bearing c
37 Retailer's cost price per bearing c
38 Wholesaler selling price per bearing c
39 Wholesaler Cost c
40 Hence, TBC (manufacturer) selling price per bearing c
41
42 Q2
43 Labor and material per bearing c
44 Commission per bearing c
45 Hence, unit variable cost per bearing or UVC c
46
47 Q3
48 Therefore $C or UVCM per bearing c
49 and %C or PVCM per bearing c
50
51 Q4
52 Production run setup c
53 Salesforce salary c
54 Salesforce travel c
55 Advertising and promotion c
56 Total Direct Fixed Costs without profit objective c
57
58 Q5
59 # BEP, without profit objective, rounded up c
60 $ BEP, without profit objective c
61
62 Q6
63 Total Direct Fixed costs, with profit objective c
64
65 Q7
66 # RLS, with profit objective, rounded up c
67 $ RLS, with profit objective c28
Upcoming Pages
Here’s what’s next.
Search Inside
This article can be searched. Note: Results may vary based on the legibility of text within the document.
Tools / Downloads
Get a copy of this page or view the extracted text.
Citing and Sharing
Basic information for referencing this web page. We also provide extended guidance on usage rights, references, copying or embedding.
Reference the current page of this Article.
Ganesh, Gopala & Paswan, Audhesh K. Teaching Basic Marketing Accountability Using Spreadsheets: An Exploratory Perspective, article, February 2010; [New York, New York]. (https://digital.library.unt.edu/ark:/67531/metadc38889/m1/30/: accessed April 24, 2024), University of North Texas Libraries, UNT Digital Library, https://digital.library.unt.edu; crediting UNT College of Business.