MBA504 MAC502 Problem Set 8 F14
MBA 504
MAC 502
Spreadsheet Modeling
Assignment #8
Prof. Liz Durango-Cohen
The data needed for this problem set is contained in worksheet “MBA_504_MAC_502_Problem_Set_#8_F14.xls”
Problem 1: Writer’s Edge is a seller of fine fountain pens. The company has found that, in spite of the growth in online buying, its catalog business is still quite strong. With the holiday season coming Writer’s Edge is planning to run a new direct mail campaign. Relevant information includes:
The cost of printing the holiday season catalog is $30,000 in fixed costs plus $0.23 per catalog.
The cost of mailing each catalog (postage + cost of purchasing names and addresses of potential customers from a list vendor) is $0.39.
Each catalog will include a direct reply envelope that can be used to send in the order form. The cost of including an envelope inside each catalog is $.02. For each envelope that is used to place an order,
Writer’s Edge incurs a prepaid postage charge of $0.26.
The average order amount is $239.
Historically, 6.5% of those receiving a catalog place an order.
The variable cost per order (cost of pen, return packaging and shipping, labor, etc.) averages 68% of the order amount.
As of this moment, Writer’s Edge plans to mail 50,000 catalogs, but that number could change once they have a better idea of the overall financial potential of the campaign.
Questions Writer’s Edge would like to answer:
What is the estimated profitability of the campaign as currently planned?
How does profit vary as response rate changes?
At what response rate does the campaign break even?
How does profit vary as the number mailed changes?
a. Set up a spreadsheet that adequately captures the key drives/inputs to analyze Writer’s Edge mailing campaign. b. Using the base-case model set up in part a, perform a what-if analysis based on the questions presented above. *This case is based on materials from “Spreadsheet Modeling and Applications: Essentials of Practical
MAC 502
Spreadsheet Modeling
Assignment #8
Prof. Liz Durango-Cohen
The data needed for this problem set is contained in worksheet “MBA_504_MAC_502_Problem_Set_#8_F14.xls”
Problem 1: Writer’s Edge is a seller of fine fountain pens. The company has found that, in spite of the growth in online buying, its catalog business is still quite strong. With the holiday season coming Writer’s Edge is planning to run a new direct mail campaign. Relevant information includes:
The cost of printing the holiday season catalog is $30,000 in fixed costs plus $0.23 per catalog.
The cost of mailing each catalog (postage + cost of purchasing names and addresses of potential customers from a list vendor) is $0.39.
Each catalog will include a direct reply envelope that can be used to send in the order form. The cost of including an envelope inside each catalog is $.02. For each envelope that is used to place an order,
Writer’s Edge incurs a prepaid postage charge of $0.26.
The average order amount is $239.
Historically, 6.5% of those receiving a catalog place an order.
The variable cost per order (cost of pen, return packaging and shipping, labor, etc.) averages 68% of the order amount.
As of this moment, Writer’s Edge plans to mail 50,000 catalogs, but that number could change once they have a better idea of the overall financial potential of the campaign.
Questions Writer’s Edge would like to answer:
What is the estimated profitability of the campaign as currently planned?
How does profit vary as response rate changes?
At what response rate does the campaign break even?
How does profit vary as the number mailed changes?
a. Set up a spreadsheet that adequately captures the key drives/inputs to analyze Writer’s Edge mailing campaign. b. Using the base-case model set up in part a, perform a what-if analysis based on the questions presented above. *This case is based on materials from “Spreadsheet Modeling and Applications: Essentials of Practical