Wilmore Paper Company
Memo: Spring 2008 Recommendation for Advertising Appropriation for Paper Towels
To: Integrated Promotion Manager, Dr. John Leckenby
From: Strategic Communication Manager, Heather Schulz
Date: March 5, 2008
In order to recommend the amount necessary to allocate to advertising expenditures for the Spring quarter of 2008, several steps were required before a conclusion could be reached. These steps are (in order):
1). Asses the response function of advertising expenditures and sales revenue for the past 30 quarters.
Click here for details.2). Apply various current effects models in order to develop parameter estimates.
Click here for details.3). Pick the most accurate current effects response function and integrate it into a net profit function.
Click here for details.4). Integrate the net profit function into a Java applet.
Click here for details.5). Utilize the Java applet to estimate an advertising expenditure that would yield a desirable net profit for the Spring 2008 quarter.
Click here for details.6). Conduct a break-even analysis in order to determine the reasonableness of the recommended advertising expenditure.
Click here for details.After the completion of the above mentioned steps, the resulting recommendation for the Spring of 2008 is as follows:
Recommended Advertising Allocation: $4,200,000
Sales Estimation: $34,609,650
Net Profit Estimation: $1,791,420
Advertising and Sales Response Function
Below are advertising expenditures and sales revenue for the past 30 quarters:
The following graph depicts the response function of advertising in relation to sales which was compiled from a time series analysis from the past 30 quarters:
Current Effects Models
The following table displays the parameter estimated as each current effects model is applied to the advertising-sales response function.
(Click here to see formulas for each current effects model.)
Since the estimation potential of these current effects models is needed, these models were only applied to the first 29 quarters of data. Then, using a procedure called “post-diction” the last quarter was utilized to test the prediction abilities for each model.
The APE denotes the post-diction effectiveness of each model.
As one can see, the Logarithmic function will be selected for the subsequent estimation of advertising expenditures because it possessed the lowest level of prediction error. The function is as follows:
Sunits = -9997.622+1750*LN(Advertising)
Where:
Sunits = Sales in units
LN = Natural Logarithm
A = Advertising Expenditures
The graph below shows the fit of the Logarithmic function to the actual data:
The chosen response function will be integrated into the following net profit function:
NP = GM – AM – A
GM = S – CGS
CGS = Sunits * C
S = Sunits * P
Sunits = -9997.622+1.75e+3*LN(Advertising)
Where:
Sunits = Sales in units (case)
S = Sales in dollars
GM = Gross Margin
A = Advertising in dollars
NP = Net Profit
P = Price ($7.52)
CGS = Cost per Goods Sold
C = Cost per case ($4.61)
AM = Administration and Marketing Expenses (0.02% increase) ($7,401,416.20)
LN = Natural Logarithm
The net profit function has been integrated into the following Java applet:
(As a reminder, you will need to have Java installed on your computer in order to view this applet).
(Click here to see Java code.)
Through trial and error, an analysis was conducted utilizing the above applet in order to determine a recommended amount of advertising expenditures in order to obtain a desirable net profit. From this analysis, the following advertising expenditures are recommended: $4,200,000. The printout for this amount is as follows:
Projected Abbreviated Statement
(in thousands)Sales $34,609.65
c.g.s. $21,216.82
______________________
Gross Margin $13,392.83 (38.6%)
Admin/Mktg. $7,401.42
ADV $4,200.00 (12.1%)
______________________
Net Profit $1,791.42 (5.17%)*predicted sales in units = 4,602.348
As one can see, spending $4,200,000 on advertising for the Spring 2008 quarter is predicted to yield:
$34,609,650in sales, an increase of 0.07% from last quarter
$13,392,830 in gross margin, an increase of 1.9% from last quarter
$1,791,420 in net profit, an increase of 1.5% from last quarter
Using the information from the Winter 2008 quarter, a break-even analysis was conducted in order to find the feasibility of the recommended advertising expenditures.
As one can see, using the estimated advertising expenditures developed by the net profit function in the Java applet ($4,200,000), in order to break even with last quarter’s profits, a minimum of $34,387,547 in sales revenue is needed to do so. Since this is a 0.06% decrease in sales, this should be an easily attainable goal, especially since the response function model predicts sales revenue to be $34,609.65.
Java code for budeting model applet:
import java.awt.BorderLayout;
import javax.swing.JPanel;
import javax.swing.JApplet;
import java.awt.Dimension;
import java.awt.GridBagLayout;
import javax.swing.JLabel;
import java.awt.Rectangle;
import javax.swing.SwingConstants;
import java.awt.Font;
import java.awt.Color;
import javax.swing.JTextField;
import javax.swing.JButton;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;import java.util.*;
import java.text.*;
public class budget extends JApplet {
String OutStuff = new String();
NumberFormat nf = NumberFormat.getCurrencyInstance(Locale.getDefault());
NumberFormat notcurrency = NumberFormat.getInstance(Locale.getDefault());
private JPanel jPanel_one = null;
private JLabel jLabel_title = null;
private JLabel jLabel_adv = null;
private JTextField jTextField_number = null;
private JButton jButton_go = null;
private JScrollPane jScrollPane_output = null;
private JTextArea jTextArea_out = null;/**
* This is the xxx default constructor
*/
public budget() {
super();
}/**
* This method initializes this
*
* @return void
*/
public void init() {
this.setSize(506, 597);
this.setContentPane(getJPanel_one());
}/**
* This method initializes jPanel_one
*
* @return javax.swing.JPanel
*/
private JPanel getJPanel_one() {
if (jPanel_one == null) {
jLabel_adv = new JLabel();
jLabel_adv.setBounds(new Rectangle(27, 61, 190, 43));
jLabel_adv.setFont(new Font("Candara", Font.BOLD, 14));
jLabel_adv.setHorizontalAlignment(SwingConstants.CENTER);
jLabel_adv.setText("Input Advertising Dollars");
jLabel_title = new JLabel();
jLabel_title.setBounds(new Rectangle(112, 5, 306, 55));
jLabel_title.setHorizontalAlignment(SwingConstants.CENTER);
jLabel_title.setFont(new Font("Harrington", Font.BOLD, 36));
jLabel_title.setText("Budgeting Model");
jPanel_one = new JPanel();
jPanel_one.setLayout(null);
jPanel_one.setBackground(new Color(208, 205, 238));
jPanel_one.add(jLabel_title, null);
jPanel_one.add(jLabel_adv, null);
jPanel_one.add(getJTextField_number(), null);
jPanel_one.add(getJButton_go(), null);
jPanel_one.add(getJScrollPane_output(), null);
}
return jPanel_one;
}/**
* This method initializes jTextField_number
*
* @return javax.swing.JTextField
*/
private JTextField getJTextField_number() {
if (jTextField_number == null) {
jTextField_number = new JTextField();
jTextField_number.setBounds(new Rectangle(213, 66, 76, 32));
}
return jTextField_number;
}/**
* This method initializes jButton_go
*
* @return javax.swing.JButton
*/
private JButton getJButton_go() {
if (jButton_go == null) {
jButton_go = new JButton();
jButton_go.setBounds(new Rectangle(314, 66, 124, 36));
jButton_go.setFont(new Font("Candara", Font.BOLD, 18));
jButton_go.setText("Click Here!");
jButton_go.addActionListener(new java.awt.event.ActionListener() {
{
//{{REGISTER_LISTENERS
SymAction lSymAction = new SymAction();
jButton_go.addActionListener(lSymAction);
//}}
}
class SymAction implements java.awt.event.ActionListener
{
public void actionPerformed(java.awt.event.ActionEvent event)
{
Object object = event.getSource();
if (object == jButton_go)
button1_ActionPerformed(event);
}
}
void button1_ActionPerformed(java.awt.event.ActionEvent event)
{
// to do: code goes here.
//first get predicted sales from response function
String tempbudget = new String();
String tempbudgetwhole = new String();
tempbudget = jTextField_number.getText();
StringTokenizer stbudget = new StringTokenizer(tempbudget,",");
while(stbudget.hasMoreTokens())
tempbudgetwhole += stbudget.nextToken();
double TotalBudget = (Double.valueOf(tempbudgetwhole)).doubleValue();
double budget = TotalBudget;
// double budget = (Double.valueOf(textfieldBudget.getText())).doubleValue();
budget /= 1000;
double predictedsales = -9997.622 + (1750 * Math.log(budget));
// below follows projected abbreviated statement construction for AB
double price = 7.52;
double salesdollars = predictedsales * price;
double admincosts = 7401.41620;
double cgs = 4.61 * predictedsales;
double grossprofit = salesdollars - cgs;
double netprofit = grossprofit - admincosts - budget;
OutStuff += ("\n\n Projected Abbreviated Statement");
OutStuff += ("\n (in thousands)");
OutStuff += ("\n\nSales " + nf.format(salesdollars));
OutStuff += ("\nc.g.s. " + nf.format(cgs));
OutStuff += ("\n ______________________");
OutStuff += ("\nGross Margin " + nf.format(grossprofit) + " " + "(" + String.valueOf((grossprofit/salesdollars) * 100.0).substring(0,4) + "%" + ")");
OutStuff += ("\nAdmin/Mktg. " + nf.format(admincosts));
OutStuff += ("\nADV " + nf.format(budget) + " " + "(" + String.valueOf((budget/salesdollars) * 100.0).substring(0,4) + "%" + ")");
OutStuff += ("\n ______________________");
OutStuff += ("\nNet Profit " + nf.format(netprofit) + " " + "(" + String.valueOf((netprofit/salesdollars) * 100.0).substring(0,4) + "%" + ")");
OutStuff += ("\n\n*predicted sales in units = " + notcurrency.format(predictedsales));
jTextArea_out.setText(OutStuff);
}
public void actionPerformed(java.awt.event.ActionEvent e) {
System.out.println("actionPerformed()"); // TODO Auto-generated Event stub actionPerformed()
}
});
}
return jButton_go;
}/**
* This method initializes jScrollPane_output
*
* @return javax.swing.JScrollPane
*/
private JScrollPane getJScrollPane_output() {
if (jScrollPane_output == null) {
jScrollPane_output = new JScrollPane();
jScrollPane_output.setBounds(new Rectangle(13, 114, 464, 459));
jScrollPane_output.setViewportView(getJTextArea_out());
}
return jScrollPane_output;
}/**
* This method initializes jTextArea_out
*
* @return javax.swing.JTextArea
*/
private JTextArea getJTextArea_out() {
if (jTextArea_out == null) {
jTextArea_out = new JTextArea();
}
return jTextArea_out;
}} // @jve:decl-index=0:visual-constraint="10,10"