Exam 11: Spreadsheet Modeling and Analysis

Which of the following formulas are used to calculate the In-house recording cost?

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 variable cost when the demand is 60,000 units.

Calculate the economic value of a loyal customer for a company given that the customer purchases, on an average, worth $43 per visit and comes three times a year. The company's gross profit margin is 35 per cent with a customer defection rate of 0.4.

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 profit when the demand is 20,00".

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 is the root cause for the newsvendor problem?

Blue Sunset Band is planning to record a new album. A major decision to be made is if the band can record the album on their own, or if they should hire a studio to record it with. The fixed cost for recording at the studio is $100,000, plus the manufacturing cost per CD, which is at $5. If they record the album in-house, the cost per CD is $10. They plan to produce 3000 copies of the album regardless of the place of recording. If the band wished to break even with the cost, how can they achieve this by using the Goal Seek feature in Excel? A B 1 The Blue Sunset Band Album Recording Decision 2 3 Data 4 5 Recording at Studio 6 Fixed Cost \ 100,000 7 Unit CD Cost \5 8 9 Recording In-house 10 Unit CD Cost \1 0 11 12 Production Volume 3000 13 14 Model 15 16 Total studio marnfacturing cost \ 115,000 17 Total in-house marnufacturing cost \ 30,000 18 19 Cost Difference \ 85,000 20 Recording Decision In-house

To meet consumer demand, Nib 'N' Ink must produce 800 gel-ink refills. Should the company produce them in-house or outsource them from the supplier? Hint: Use the mathematical descriptive model).

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 variable cost of production.

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 -Which of the following is the excel formula used to estimate overbooked customers?

When will a company use a predictive decision model?

Explain how a two-way data table is created.

Which of the following formula is used to make the recording decision in B20?

Which of the following would be used to calculate the gross profit?

Troista Mobile Accessories sells mobile apps on their Web site. If a customer spends on average, $12 per visit and visits the Web site 20 times each year, what is the average nondiscounted gross profit during a customer's lifetime? Given that Troista makes a margin of 60 percent on the average bill, with 25 percent of customers not returning each year.

How can managers judge the validity of a model?

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 profit for the third year.

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's the total retirement balance when Sheila has reached the age of 40 while working with Simsin?

Give an account of how the design and format of spreadsheets can be improved.

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 share percentage in the third 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 -Calculate the net profit for the headphones.