Each problem is worth up to 10 points each for a maximum 60 points for the set.
Please do problems 1 – 4 on Excel. You may do problems 5 and 6 using any application you like, or you may submit them in handwritten form.
A couple of reminders when you do these problems in Excel
1) For wider spreadsheets, use Landscape, not Portrait page orientation.
2) For your numbers, decide how many decimal points you want to show, and force the cell formatting to that. Don’t stay with the General Format that Excel has as a default. All numbers of the same type, e.g. sales, trend, and FC, should all have the same number of decimal points so that the reader’s eye can easily compare.
3) When you have a large number of columns, set the width to just slightly more than the numbers, otherwise the extra white space will make the table even wider. If you see ### in the cell, it means your column is too narrow.
4) Force page breaks where you want them. Don’t break a problem across two pages unless it really won’t fit on one, and then, choose where it should break.
1. Big Reference Books Inc. has seen its unit sales decline due to the increased use of the World Wide Web as a reference source. Below are its unit sales for the last 6 quarters:
Using Excel, plot the data on a graph and calculate the equation for that trend line. Using that equation, calculate the forecast for the next two quarters, ignoring seasonality. Show the trend line and the two forecast points on your graph.
2. Jensen Equipment Company is trying to forecast next year’s quarterly sales. The last tow years of sales (in units) are shown below:
Quarter 1 2 3 4 5 6 7 8 9 10 11 12
Unit Sales 179 205 150 140 225 240 190 185
a. Using Excel, plot the data and fit a linear regression trend line to it. Show the equation for the trend line.
b. Using that regression formula, calculate the forecast for each of the next 4 quarters, ignoring seasonality.
c. Calculate the seasonality factor for each quarter and calculate the seasonally adjusted forecast for the next 4 quarters.
3. Green Grass Sprinkler Co makes pop-up sprinkler heads used in residential installations. The companyis evaluating its capacity requirements for the next four years based on its updated sales forecast.
The company has two production lines, one for the three models of plastic sprinkler heads and one for the three models of bronze heads. The current demand forecast is as follows:
The production line for the plastic sprinklers has one injection-molding machine capable of producing 200,000 units a year. This machine requires four operators. The production line for the bronze sprinklers has three machines, each capable of producing 12,000 sprinkler heads per year. Each machine requires two operators.
For each production line, show
a) The % capacity utilization of the line each year.
b) How many machines (including fractional) are needed each year.
For an extra bonus of two points:
c) As a manager looking over these results, is there anything that would disturb you here?
4. Green Grass Sprinkler Co – Increased Bronze Demand
A marketing intern has proposed a campaign to increase demand for the bronze sprinklers. Because of the intern’s strong logic and persuasive communication skills management has accepted the proposal and asked the manufacturing department if they can meet this increased demand assuming the campaign is successful. Can they? Show your work.
The increased forecast for bronze sprinklers demand is as follows:
Bronze Demand – Units in Thousands
Year 1 Year 2 Year 3 Year 4
Bronze 90 11 15 18 23
Bronze 180 6 5 6 9
Bronze 360 15 16 17 20
5. Family Furniture Company is considering expanding its business by either enlarging its current factory or by adding a second factory. Expanding the current factory would add $1.5 million in additional overhead per year, while a new factory would increase overhead expenses by $3.2 million.
If the company expands its current location, a new factory would add $ 3.5 million in additional gross profit per year if demand growth is strong, and $2.5 million if demand growth is weak.
With a new factory, the company would have an additional $6.4 million in gross profit per year if demand growth is strong, and $4.2 million if growth is weak.
FFC estimates there is a 60% chance that demand growth will be strong whichever option they choose. What should FFC do? Draw the decision tree and show your work.
6. Hawthorne Electronics Corp. – International Expansion Decision
Hawthorne Electronics Corp (HEC) is considering expanding its successful US semiconductor chip business into Europe, and is considering two options.
If HEC sets up its own European Sales Division, it will incur additional operating expenses of $600,000 per year. There is a 60% chance that demand will be high, in which case the sales division will sell $20 million per year in Europe at a gross margin of 20%. If demand is low (40% probability), it will sell only $15 million per year at a lower margin of 15%.
If the company uses a local distributor, it will not incur additional overhead, but its prices to the distributor will be lower (the distributor is incurring selling expenses). This will result in revenue for HEC on sales to its distributor of $18 million with a gross margin of 15% if demand is high and $12 million with a gross margin of 10% if demand is low. The probability that demand will be high or low as the same as for the first case.
What should Hawthorne do? Draw a decision tree and show your work.
