We began our analysis by first examining the data provided to us in the Excel file. We were given the percent of customs who purchased Harry Potter book 7 and what price they paid. We then graphed the data points using a scatter plot to identify a trend. In order to determine the predicted sales for the Harry Potter sequel we first had to use a power regression. A regression is a technique that attempts to measure the relationship between the outcome variable and the explanatory variables, or dependent versus independent variables. A power curve allows us to examine economies of scale and diseconomies of scale. Economies of scale means that there is a reduced cost per unit as unit volume increases. A visual of the power regression is included
…show more content…
The equation of the line was estimated as y=15.037x-1.901 and the R2 value was 0.994. The R2 value defines what percent of variation in (y) can be determined by variation in (x). In other words, it is a statistical measure that determines how close the data is to the regression line. Ideally you want to achieve an R2 close to one however, caution should be exercised when the value is significantly high or very close to one. When R2 is equal to one, it means that the regression model explains all of the variation of the data. A lower R2 means that the model explains less of the variability of the data. Our power regression resulted in an R2 value of 0.994. This number is extremely close to one which signals us to be cautious moving forward as a high R2 value isn’t always good. After re-running the regression multiple times we were able to become satisfied that this estimate of the R2 value was …show more content…
As indicated in Exhibit 2, we used the conditional formatting feature in Excel to highlight the profit column. This feature allows us to quickly analyze the data and see which books were the most profitable and which were the least profitable. The darker shades are the least profitable books while the light shades represent our most profitable books. Conditional formatting also allows you to quickly identify patterns in the data instead of having to carefully analyze all of the numbers. As you can see from our formatting, as the book price increases, we generally earn more profit. This continues up until a certain point, however. As we discussed before, once the book price gets too high, the demand drops, and we lose profits. We will discuss this topic in further detail in the next section.
To determine the maximum profit we could make when selling the book at different price points and varying the publishers cost, we ran three optimizations in Excel. We used the Solver tool within Excel as it allowed us to maximize profit while considering any constraints that might apply. We have included Exhibit 3 below to display the three optimizations we ran in Excel. The first line is highlighted to emphasize the final results from each optimization. These optimizations will be explained in further detail
The equation of the line was estimated as y=15.037x-1.901 and the R2 value was 0.994. The R2 value defines what percent of variation in (y) can be determined by variation in (x). In other words, it is a statistical measure that determines how close the data is to the regression line. Ideally you want to achieve an R2 close to one however, caution should be exercised when the value is significantly high or very close to one. When R2 is equal to one, it means that the regression model explains all of the variation of the data. A lower R2 means that the model explains less of the variability of the data. Our power regression resulted in an R2 value of 0.994. This number is extremely close to one which signals us to be cautious moving forward as a high R2 value isn’t always good. After re-running the regression multiple times we were able to become satisfied that this estimate of the R2 value was …show more content…
As indicated in Exhibit 2, we used the conditional formatting feature in Excel to highlight the profit column. This feature allows us to quickly analyze the data and see which books were the most profitable and which were the least profitable. The darker shades are the least profitable books while the light shades represent our most profitable books. Conditional formatting also allows you to quickly identify patterns in the data instead of having to carefully analyze all of the numbers. As you can see from our formatting, as the book price increases, we generally earn more profit. This continues up until a certain point, however. As we discussed before, once the book price gets too high, the demand drops, and we lose profits. We will discuss this topic in further detail in the next section.
To determine the maximum profit we could make when selling the book at different price points and varying the publishers cost, we ran three optimizations in Excel. We used the Solver tool within Excel as it allowed us to maximize profit while considering any constraints that might apply. We have included Exhibit 3 below to display the three optimizations we ran in Excel. The first line is highlighted to emphasize the final results from each optimization. These optimizations will be explained in further detail