Exam 11: Spreadsheet Modeling and Analysis

In predictive modeling, validity refers to how well a model represents reality.

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 cumulative net profit at the fourth year.

Use the table below to answer the following questions). Sujito Electronix makes headphones for $22 and sells them for $32. Sujito has sold at least 50 headphones on average per week in the past, though the actual demand is unknown. Sujito has also often run short of supply in the past. After three months of release, the headphones are sold at 40 percent discount. The spreadsheet below shows Sujito's sales and demand for the headphones. We take demand at 51, and quantity produced at 55. Newsvendor model for Sujito's headphones Data SellingPrice \ 32 Cost \ 22 Discount Price \ 19.2 Model Demand 51 Produced Quantity 55 Quantity Sold Surplus Quantity -Which of the following is the value for quantity sold?

Which of the following formulas would be used to calculate the net income value using only the data value?

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 total cost when demand is 40,000.

The process of developing good, useful, and correct spreadsheet models is known as spreadsheet engineering.

Which of the following is the mathematical model for deriving total cost of only manufacturing?

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

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}$ -Calculate the total revenue when the quantity produced is 55,000 and demand is 60,000.

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 net revenue.

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 net profit when the demand is 65,000.

Which of the following formulas would be used to calculate earnings before taxes?

Profit is computed as the difference between total revenue from sales and total cost of production. Revenue depends on the market share captured by a company and the size of the target market. Various marketing decisions taken by managers are reflected upon the market share. The decisions also have an impact on marketing costs which in turn will affect the total cost of production. Construct an influence diagram that relates these variables.

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 conditions is the optimal solution to the newsvendor problem, where Q is the quantity to be purchased, and D is demand?

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 market size for the second year.

Which of the following is necessary to calculate the variable cost of production for the company to develop a profit model?

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 the amount of employee contribution to retirement plan when Sheila has reached the age of 38?

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 cost incurred for the second year.

Gruten Retailers sells Mother's Day special greeting cards at their store at $6. They make the cards for a dollar apiece. Most of the cards are sold by Mother's Day, but the actual demand is unknown. They have orders for 120 cards. In the past, they have had sales of at least 100 cards by Mother's Day. The remaining cards are sold at a 40 percent discount. Calculate the net profit, if demand, D, is set at 110 units.

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 projected sales for the first year.