Answered step by step
Verified Expert Solution
Question
...
1 Approved Answer
Part 1: An influence diagram outlining the problem. You may use Excel, Powerpoint, Visio, or any other tool for your diagram. Part 2: An Excel
-
Part 1: An influence diagram outlining the problem.
-
You may use Excel, Powerpoint, Visio, or any other tool for your diagram.
-
Part 2: An Excel Workbook that structures the problem.
-
NOTE: You do NOT need to optimize this problem. You just need to set it up so that someone could solve the problem manually, within Excel. More precisely, the user should be able to set the amount of money allocated in each quarter, and your spreadsheet should show the profit.
EXAMPLE As product-marketing manager, one of our jobs is to prepare recommendations to the The Executive Committee as to how advertising expenditures should be allocated. Last year's Advertising advertising budget of $40,000 was spent in equal increments over the four quarters. Initial Budget expectations are that we will repeat this plan in the coming year. However, the committee Decision would like to know whether some other allocation would be advantageous and whether the total budget should be changed. Our product sells for $40 and costs us $25 to produce. Sales in the past have been seasonal, and our consultants have estimated seasonal adjustment factors for unit sales as follows: 01 : 90% 03 : 80% 02:110% 04 : 120% (A seasonal adjustment factor measures the percentage of average quarterly demand experienced in a given quarter.) In addition to production costs, we must take into account the cost of the sales force (projected to be $34,000 over the year, allocated as follows: 01 and 02, $8,000 each; 03 and 04, $9,000 each), the cost of advertising itself, and overhead (typically around 15 percent of revenues). Quarterly unit sales seem to run around 4,000 units when advertising is around $10,000. Clearly, advertising will increase sales, but there are limits to its impact. Our consultants several years ago estimated the relationship between advertising and sales. Converting that relationship to current conditions gives the following formula: Unit sales = 35 x seasonal factor X (3,000 + Advertising) EXAMPLE As product-marketing manager, one of our jobs is to prepare recommendations to the The Executive Committee as to how advertising expenditures should be allocated. Last year's Advertising advertising budget of $40,000 was spent in equal increments over the four quarters. Initial Budget expectations are that we will repeat this plan in the coming year. However, the committee Decision would like to know whether some other allocation would be advantageous and whether the total budget should be changed. Our product sells for $40 and costs us $25 to produce. Sales in the past have been seasonal, and our consultants have estimated seasonal adjustment factors for unit sales as follows: 01 : 90% 03 : 80% 02:110% 04 : 120% (A seasonal adjustment factor measures the percentage of average quarterly demand experienced in a given quarter.) In addition to production costs, we must take into account the cost of the sales force (projected to be $34,000 over the year, allocated as follows: 01 and 02, $8,000 each; 03 and 04, $9,000 each), the cost of advertising itself, and overhead (typically around 15 percent of revenues). Quarterly unit sales seem to run around 4,000 units when advertising is around $10,000. Clearly, advertising will increase sales, but there are limits to its impact. Our consultants several years ago estimated the relationship between advertising and sales. Converting that relationship to current conditions gives the following formula: Unit sales = 35 x seasonal factor X (3,000 + Advertising)
Part 1: An influence diagram outlining the problem.
-
You may use Excel, Powerpoint, Visio, or any other tool for your diagram.
Part 2: An Excel Workbook that structures the problem.
-
NOTE: You do NOT need to optimize this problem. You just need to set it up so that someone could solve the problem manually, within Excel. More precisely, the user should be able to set the amount of money allocated in each quarter, and your spreadsheet should show the profit.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started