Financial Management - Excel Required for Answer

Required Course Textbook(s)

· Management Science, 14th Edition

By: Anderson/Cochran/Fry/Ohlmann ISBN: 978-1-337-03485-2

Format: Answer these questions using an Excel workbook and include a problem per sheet. Name the sheets with the corresponding problem number in the textbook, for instance 1-14 or 4-19. Many problems may include a solver solution. Follow the format in the excel document in Course Resources and used in class. Some problems in the textbook have additional questions that require a change some conditions in your solver set up, just copy the original problem to a new worksheet and solve the new conditions. Do not change the conditions in the initial solver set up, so the instructor can see all your work.

· Week 2

· Assignments:

· Distribution & Network Models (Ch 6) – Problems #7, #9

Problem 7:. Aggie Power Generation supplies electrical power to residential customers for many U.S. cities. Its main power generation plants are located in Los Angeles, Tulsa, and Seattle. The following table shows Aggie Power Generation’s major residential markets, the annual demand in each market (in megawatts or MW), and the cost to supply electricity to each market from each power generation plant (prices are in $/MW).

a. If there are no restrictions on the amount of power that can be supplied by any of the power plants, what is the optimal solution to this problem? Which cities should be supplied by which power plants? What is the total annual power distribution cost for this solution?

b. If at most 4000 MW of power can be supplied by any one of the power plants, what is the optimal solution? What is the annual increase in power distribution cost that results from adding these constraints to the original formulation?

Problem 9:. The Ace Manufacturing Company has orders for three similar products:

Three machines are available for the manufacturing operations. All three machines can produce all the products at the same production rate. However, due to varying defect per-centages of each product on each machine, the unit costs of the products vary depending on the machine used. Machine capacities for the next week and the unit costs are as follows:

Use the transportation model to develop the minimum cost production schedule for the products and machines. Show the linear programming formulation.

· Project Scheduling (Ch 9) – Problems #7, #11, #12, #21, #22

Problem 7:. refer to the gasoline sales time series data in Table 15.1.

a. Compute four-week and five-week moving averages for the time series.

b. Compute the MSE for the four-week and five-week moving average forecasts.

c. What appears to be the best number of weeks of past data (three, four, or five) to use in the moving average computation? recall that MSE for the three-week moving average is 10.22.

Problem 11: For the Hawkins Company, the monthly percentages of all shipments received on time over the past 12 months are 80, 82, 84, 83, 83, 84, 85, 84, 82, 83, 84, and 83.

a. Construct a time series plot. What type of pattern exists in the data?

b. Compare a three-month moving average forecast with an exponential smoothing fore-cast for a5 0.2. Which provides the better forecasts using MSE as the measure of model accuracy?

c. What is the forecast for next month?

Problem 12: Corporate triple a bond interest rates for 12 consecutive months follow.9.59.39.49.69.89.79.810.59.99.79.69.6

a. Construct a time series plot. What type of pattern exists in the data?

b. Develop three-month and four-month moving averages for this time series. does the three-month or four-month moving average provide the better forecasts based on MSE? explain.

c. What is the moving average forecast for the next month?

Problem 21: The Centers for disease Control and Prevention office on Smoking and Health (OSH) is the lead federal agency responsible for comprehensive tobacco prevention and control. OSH was established in 1965 to reduce the death and disease caused by tobacco use and exposure to second-hand smoke. one of the many responsibilities of the OSH is to collect data on tobacco use. The following data show the percentage of adults in the United States who were users of tobacco from 2001 through 2011 (http://www.cdc.gov /tobacco/data statistics/tables/trends/cig_smoking/index.htm).

a. Construct a time series plot. What type of pattern exists in the data?

b. use simple linear regression to find the parameters for the line that minimizes MSE for this time series.

c. one of OSH’s Healthy People 2020 Goals is to cut the percentage of adults in the United States who were users of tobacco to 12% or less by the year 2020. does your regression model from part (b) suggest that the OSH is on target to meet this goal? if not, use your model from part (b) to estimate the year in which the OSH will achieve this goal.

Problem 22: The president of a small manufacturing firm is concerned about the continual in-crease in manufacturing costs over the past several years. The following figures provide a time series of the cost per unit for the firm’s leading product over the past eight

years.

a. Construct a time series plot. What type of pattern exists in the data?

b. use simple linear regression analysis to find the parameters for the line that minimizes MSE for this time series.

c. What is the average cost increase that the firm has been realizing per year?

d. Compute an estimate of the cost/unit for next year.