Reference no: EM13376186

QUESTION 

Dr Catscan is an ophthalmologist who, in addition to prescribing glasses and contact lenses, performs laser surgery to correct myopia. This laser surgery is fairly easy and inexpensive to perform.

To inform the public about this procedure Dr Catscan advertises in the local paper and holds information sessions in her office one night a week at which she shows a videotape about the procedure and answers any questions potential patients might have.

The room where these meetings are held can seat 10 people, and reservations are required. The number of people attending each session varies from week to week. Dr Catscan cancels the meeting if 2 or fewer people have made reservations.

Using data from the previous year Dr Catscan determined that reservations follow this pattern:

Number of reservations	0	1	2	3	4	5	6	7	8	9	10
Probability	0.02	0.05	0.08	0.16	0.26	0.18	0.11	0.07	0.05	0.01	0.01

Using the data from last year Dr Catscan determined that 25% of the people who attended information sessions elected to have the surgery performed. Of those who do not, most cite the cost of the procedure ($2,000 per eye, $4,000 in total as almost everyone has both eyes done) as their major concern. The surgery is regarded as cosmetic so that the cost is not covered by Medicare or private hospital insurance funds.

Dr Catscan has now hired you as a consultant to analyse her financial returns from this surgery. In particular, she would like answers to the following questions, which you are going to answer by building an Excel model to simulate 20 weeks of the practice. Random numbers must be generated in Excel and used with the VLOOKUP command to determine the number of reservations,0 and there must be no data in the model itself. The same set of random numbers should be used for all three parts. An IF statement is required for part (a) to determine attendance each week, given cancellation of meetings.

(a) On average, how much revenue does Dr Catscan's practice in laser surgery earn each week? If your simulation shows a fractional number of people electing surgery use such fractional values in determining revenue. Paste your model results into Word including a copy of formulas with row and column headings.

(b) Adjust your model to determine on average, how much revenue would be generated each week if Dr Catscan did not cancel sessions with 2 or fewer reservations? Paste results into Word.

(c) Dr Catscan believes that 35% of people attending the information sessions would have the surgery if she reduced the price to $1,500 per eye or $3,000 in total. Under this scenario how much revenue per week could Dr Catscan expect from laser surgery? Modify your Excel model to answer this and paste results into Word.

(d) Write a brief report with your recommendations to Dr Catscan on the most appropriate strategy.

QUESTION Regression Analysis and Cost Estimation

The CEO of Carson Company has asked you to develop a cost equation to predict monthly overhead costs in the production department. You have collected actual overhead costs for the last 12 months, together with data for three possible cost drivers, number of Indirect Workers, number of Machine Hours worked in the department and the Number of Jobs worked on in each of the last 12 months:

Overhead Costs	Indirect Workers	Machine Hours	Number of Jobs
$2,200	30	350	1,000
4,000	35	500	1,200
3,300	50	250	900
4,400	52	450	1,000
4,200	55	380	1,500
5,400	58	490	1,100
5,600	90	510	1,900
4,300	70	380	1,400
5,300	83	350	1,600
7,500	74	490	1,650
8,000	100	560	1,850
10,000	105	770	1,250

(a) The CEO suggests that he has heard that the high-low method of estimating costs works fairly well and should be inexpensive to use. Write a response to this suggestion for the CEO indicating the advantages and disadvantages of this method. Include the calculation of a cost equation for this data using Machine Hours as the cost driver.

(b) Using Excel develop three scatter diagrams showing overhead costs against each of the three proposed independent variables. Comment on whether these scatter diagrams appear to indicate that linearity is a reasonable assumption for each.

(c) Using the regression module of Excel's Add-in Data Analysis, perform 3 simple regressions by regressing overhead costs against each of the proposed independent variables. Show the output for each regression and evaluate each of the regression results, indicating which of the three is best and why.

Provide the cost equations for those regression results which are satisfactory and from them calculate the predicted overhead in a month where there were 100 Indirect Workers and 500 Machine Hours and 1,000 Jobs worked.

(d) Selecting the two best regressions from part (c) conduct a multiple regression of overhead against these two independent variables. Evaluate the regression results.

Draw conclusions about the best of the four regression results to use.

QUESTION Forecasting

(a)

All forecasts are never 100% accurate but subject to error.

1. How is forecast error calculated?
2. Identify and describe three common measures of forecast error. Then illustrate how each is calculated by constructing a 4-period example.

(b) Consider the following table of monthly sales of car tyres by a local company:

Month Unit Sales
January	400
February	500
March	540
April	560
May	600
June	?

(i) Using a 2-month moving average develop forecasts sales for March to June inclusive.

(ii) Using a 2-month weighted moving average, with weights of 2 for the most recent month and 1 for the previous month develop forecasts sales for March to June inclusive.

(iii) The sales manager had predicted sales for January of 400 units. Using exponential smoothing with a weight of 0.3 develop forecasts sales for March to June inclusive.

(iv) Which of the three techniques gives the most accurate forecasts? How do you know?

Describe the four patterns typically found in time series data. What is meant by the expression "decomposition" with regard to forecasting? Briefly describe the process.