Exam 11: Spreadsheet Modeling and Analysis

Use the table below to answer the following questions). Dresden Pharmaceuticals has decided to go ahead and start clinical trials on a potential new drug. The total R&D costs are estimated to reach around $875,000,000 with clinical trials mounting to $145,000,000. The current market size is estimated to be around 3,000,000 and is expected to grow at 4 percent every year. The market share Dresden hopes to capture in the first year is 7 percent, and is projected to grow by 25 percent each year for the next 4 years. A monthly prescription is anticipated to generate revenue of $420 while incurring variable costs of $150. A discount rate of 8 percent is assumed. $\begin{array} { | l | l | } \hline \text { Dresden Pharmaceuticals } & \\\hline & \\\hline \text { Data } & \\\hline & \\\hline \text { Market Size } & 3,000,000 \\\hline \text { Unit monthly Rx) revenue \$) } & 420 \\\hline \text { Unit monthly Rx) cast \$) } & 150 \\\hline \text { Discount Rate per cent) } & 8 \\\hline & \\\hline \text { Project Costs } & \\\hline \text { R\&D \$) } & 875,000,000 \\\hline \text { Clinical Trials \$) } & 145,000,000 \\\hline & \\\hline\end{array}$ -Calculate the net present value for Dresden's new drug.

Use the table below to answer the following questions). Below is a room overbooking model spreadsheet for the Metza, a hotel chain. The hotel has 425 rooms priced at $180 per day each, and is usually fully booked. Reservations can be cancelled any time before 5:00 p.m. with no penalty. The hotel estimates an average overbooking cost of $150. Customer demand is set at 400 with an average cancellation of 20. A B Hotel Overbonking Model for the Metza group af hotels 1 2 Data 3 4 5 Rooms Available 425 6 Price per room \ 180 7 overbonking Cost \ 150 8 9 10 11 Reservation Limit 425 12 Customer Demand 400 13 Reservation Made 14 Cancellations 20 15 Customer Arrivals 16 Overbonked Custarers -Calculate the customer arrivals at the Metza.

Which of the following formulas would be used to calculate the operating expenses?

Brenton joined the Kroos Corporation at a starting salary of $61,500. According to the company's retirement plan, Brenton has to make a retirement contribution of 6 percent of his salary. The company contributes 30 percent of this amount. Brenton is expected to receive a salary increment of 3.5 percent per year for the next three years. Brenton is also expected to receive an annual investment return of 8 percent on the plan. Assuming the same rate of salary increases and investment returns each year, calculate the total balance of the retirement plan in its second year.

Two-way data tables can evaluate only one output variable.

Use the table below to answer the following questions). Sheila joined Simsin Tradings at the age of 36 with a starting salary of $75,000. She expects a salary increase of 5 percent every year. Her retirement plan requires her to pay 9 percent of her salary, while the company matches it at 32 percent. She expects an annual return of 7 percent on her retirement portfolio. Using a predictive model for Sheila's first five years, calculate the following, assuming that the salary increases at the same rate every year, and the return of interest does not change. Retir ement Plan Model for Sheila Data Retirement Contribution percent of salary) 9 percent Employer Match 32 percent Annual Salary Increase 5 percent Annual Return on Investment 7 percent -Calculate the employer contribution in Sheila's fourth year at Simsin.

Explain how the Data Validation feature in Excel helps in increasing spreadsheet quality.

In which of the following ways does demand influence profit?

Use the table below to answer the following questions). Dresden Pharmaceuticals has decided to go ahead and start clinical trials on a potential new drug. The total R&D costs are estimated to reach around $875,000,000 with clinical trials mounting to $145,000,000. The current market size is estimated to be around 3,000,000 and is expected to grow at 4 percent every year. The market share Dresden hopes to capture in the first year is 7 percent, and is projected to grow by 25 percent each year for the next 4 years. A monthly prescription is anticipated to generate revenue of $420 while incurring variable costs of $150. A discount rate of 8 percent is assumed. $\begin{array} { | l | l | } \hline \text { Dresden Pharmaceuticals } & \\\hline & \\\hline \text { Data } & \\\hline & \\\hline \text { Market Size } & 3,000,000 \\\hline \text { Unit monthly Rx) revenue \$) } & 420 \\\hline \text { Unit monthly Rx) cast \$) } & 150 \\\hline \text { Discount Rate per cent) } & 8 \\\hline & \\\hline \text { Project Costs } & \\\hline \text { R\&D \$) } & 875,000,000 \\\hline \text { Clinical Trials \$) } & 145,000,000 \\\hline & \\\hline\end{array}$ -Which of the following years shows the first profit for Dresden's new drug?

Use the table below to answer the following questions). Below is the profit model spreadsheet for the Lazarus Shoe Company producing their latest model of shoes for the month of January. Profit Model for Lazarus Shoe Company for Tanuary All cost in \ ) Unit Price 47 Unit Cost 22 Fixed Cost for Production 350,000 Demand 40,000 Model Unit Price 47 Quantity Sold 38,000 Revenue Unit Cost 22 Quantity Produced 38,000 Variable Cost Fixed Cost 350,000 Profit -Calculate the total profit.

Find the range of volumes at which it is more economical for Nib 'N' Ink to produce the refills in-house or outsource them. Hint: Use the break-even decision model).

Use the table below to answer the following questions). In the spreadsheet below, there is data on the price, cost, demand, and quantity produced for an item. There are also different "what if" values that can help a manager to calculate costs and revenue with variability in demand. $\begin{array} { | l | l | l | l | } \hline & \text { A } & \text { B } & \text { C } \\\hline 1 & \text { Profit Madel } & & \\\hline 2 & & & \\\hline 3 & & & \text { What } \text { if Demand } \\& \text { Data } & & \text { Values } \\\hline 4 & & & 20,000 \\\hline 5 & \text { Unit Price \$) } & 50 & 40,000 \\\hline 6 & \text { Unit Cost \$) } & 25 & 55,000 \\\hline 7 & \text { Fixed Cost \$) } & 550,000 & 60,000 \\\hline 8 & \text { Demand } & 60,000 & 65,000 \\\hline 9 & \text { Quantity Produced } & 55,000 & \\\hline 10 & & & \\\hline\end{array}$ -From the "what if" values, calculate the revenue if the demand is 60,000 units.

Use the table below to answer the following questions). Below is the profit model spreadsheet for the Lazarus Shoe Company producing their latest model of shoes for the month of January. Profit Model for Lazarus Shoe Company for Tanuary All cost in \ ) Unit Price 47 Unit Cost 22 Fixed Cost for Production 350,000 Demand 40,000 Model Unit Price 47 Quantity Sold 38,000 Revenue Unit Cost 22 Quantity Produced 38,000 Variable Cost Fixed Cost 350,000 Profit -Calculate the revenue for units sold.

Use the table below to answer the following questions). Sheila joined Simsin Tradings at the age of 36 with a starting salary of $75,000. She expects a salary increase of 5 percent every year. Her retirement plan requires her to pay 9 percent of her salary, while the company matches it at 32 percent. She expects an annual return of 7 percent on her retirement portfolio. Using a predictive model for Sheila's first five years, calculate the following, assuming that the salary increases at the same rate every year, and the return of interest does not change. Retir ement Plan Model for Sheila Data Retirement Contribution percent of salary) 9 percent Employer Match 32 percent Annual Salary Increase 5 percent Annual Return on Investment 7 percent -What will be Sheila's salary in her second year of work at Simsin?

Which of the following formulas is used to calculate the total studio recording cost?

Discuss how overbooking decisions are made by service businesses.

Give an account of how data is used in predictive models.

Use the table below to answer the following questions). Dresden Pharmaceuticals has decided to go ahead and start clinical trials on a potential new drug. The total R&D costs are estimated to reach around $875,000,000 with clinical trials mounting to $145,000,000. The current market size is estimated to be around 3,000,000 and is expected to grow at 4 percent every year. The market share Dresden hopes to capture in the first year is 7 percent, and is projected to grow by 25 percent each year for the next 4 years. A monthly prescription is anticipated to generate revenue of $420 while incurring variable costs of $150. A discount rate of 8 percent is assumed. $\begin{array} { | l | l | } \hline \text { Dresden Pharmaceuticals } & \\\hline & \\\hline \text { Data } & \\\hline & \\\hline \text { Market Size } & 3,000,000 \\\hline \text { Unit monthly Rx) revenue \$) } & 420 \\\hline \text { Unit monthly Rx) cast \$) } & 150 \\\hline \text { Discount Rate per cent) } & 8 \\\hline & \\\hline \text { Project Costs } & \\\hline \text { R\&D \$) } & 875,000,000 \\\hline \text { Clinical Trials \$) } & 145,000,000 \\\hline & \\\hline\end{array}$ -Calculate the annual revenue for the fourth year.

Which of the following formulas would be used to calculate the net income value using only the information in the Model, and not in the Data section?

Which decision model incorporates the uncertainty element?