Exam 11: Spreadsheet Modeling and Analysis

Use a modern software tool to perform statistical calculations. Use the table below to answer the following question(s). Fiberia Accessories, a clothing retailer, is planning to introduce a new line of sweaters as part of the winter collection for $65 with an inventory of 1500. The main selling season is 60 days between November and December. The store then sells the remaining units in a clearance sale at 65 percent discount. Out of the 60 main retail days, Fiberia sells the sweaters at full retail price for only 45 days, while giving a discount of 25 percent for the remaining 15 days. The demand functions a, and b are given as 79.5 and 1.1 respectively. Marked Down Pricing Model for Fiberia Accessories's new sweater Data Retail Price Inventory \ 65 Selling Season (days) 1500 Days at Full Retail 60 Intermediate Markdown 45 Clearance Markdown 25 percent Demand Function 65 percent A B 79.5 -Calculate the daily sales during the discount sales period.

Use a modern software tool to perform statistical calculations. Use the table below to answer the following question(s). Fiberia Accessories, a clothing retailer, is planning to introduce a new line of sweaters as part of the winter collection for $65 with an inventory of 1500. The main selling season is 60 days between November and December. The store then sells the remaining units in a clearance sale at 65 percent discount. Out of the 60 main retail days, Fiberia sells the sweaters at full retail price for only 45 days, while giving a discount of 25 percent for the remaining 15 days. The demand functions a, and b are given as 79.5 and 1.1 respectively. Marked Down Pricing Model for Fiberia Accessories's new sweater Data Retail Price Inventory \ 65 Selling Season (days) 1500 Days at Full Retail 60 Intermediate Markdown 45 Clearance Markdown 25 percent Demand Function 65 percent A B 79.5 -Calculate the total number of units sold during the full retail sales period.

Which of the following is the root cause for the newsvendor problem?

Use a modern software tool to perform statistical calculations. Use the table below to answer the following question(s). Fiberia Accessories, a clothing retailer, is planning to introduce a new line of sweaters as part of the winter collection for $65 with an inventory of 1500. The main selling season is 60 days between November and December. The store then sells the remaining units in a clearance sale at 65 percent discount. Out of the 60 main retail days, Fiberia sells the sweaters at full retail price for only 45 days, while giving a discount of 25 percent for the remaining 15 days. The demand functions a, and b are given as 79.5 and 1.1 respectively. Marked Down Pricing Model for Fiberia Accessories's new sweater Data Retail Price Inventory \ 65 Selling Season (days) 1500 Days at Full Retail 60 Intermediate Markdown 45 Clearance Markdown 25 percent Demand Function 65 percent A B 79.5 -Calculate the total units sold during the discount sales period.

Use the table below to answer the following question(s). 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) cost (\$) } & 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.

Use the table below to answer the following question(s). 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 Model } & & \\\hline 2 & & & \\\hline 3 & \text { Data } && \begin{array}{l}\text { What-If } \text { Demand } \\\text { Values }\end{array} \\\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}$ -Which of the following is the Excel formula to determine the number of units sold?

Use a modern software tool to perform statistical calculations. Using the spreadsheet below to answer the following question(s). The spreadsheet below shows the net income model for a company that sells shoes. A B 1 Net Income Model 2 3 Data 4 5 Sales \ 10,000,000 6 Cost of Goods Sold \ 6,400,000 7 Administrative Expenses \ 500,000 8 SellingExpenses \ 900,000 9 Depreciation Expenses \ 750,000 10 Interest Expenses \ 70,000 11 Taxes \ 620,000 12 13 Model 14 15 Gross Profit \ 3,600,000 16 Operating Expenses \ 2,150,000 17 Net Operating Income \ 1,450,000 18 Eamings Before Taxes \ 1,380,000 19 20 Net Income -Which of the following formulas would be used to calculate earnings before taxes?

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

Use a modern software tool to perform statistical calculations. Use the table below to answer the following question(s). Fiberia Accessories, a clothing retailer, is planning to introduce a new line of sweaters as part of the winter collection for $65 with an inventory of 1500. The main selling season is 60 days between November and December. The store then sells the remaining units in a clearance sale at 65 percent discount. Out of the 60 main retail days, Fiberia sells the sweaters at full retail price for only 45 days, while giving a discount of 25 percent for the remaining 15 days. The demand functions a, and b are given as 79.5 and 1.1 respectively. Marked Down Pricing Model for Fiberia Accessories's new sweater Data Retail Price Inventory \ 65 Selling Season (days) 1500 Days at Full Retail 60 Intermediate Markdown 45 Clearance Markdown 25 percent Demand Function 65 percent A B 79.5 -Calculate the total revenue for the new line of sweaters.

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

In which of the following ways does demand influence profit?

Identify different business uses for statistics and the major statistical tools businesses use Use the table below to answer the following question(s). 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 Selling Price \ 32 Cost \ 22 Discount Price \ 19.2 Model Demand 51 Produced Quantity 55 Quantity Sold Surphus Quantity -Calculate the net profit for the headphones.

Use a modern software tool to perform statistical calculations. Use the table below to answer the following question(s). Below is the profit model spreadsheet for the Lazarus Shoe Company producing their latest model of shoes for the month of January. $\begin{array}{|l|l|}\hline\begin{array}{l}\text { Profit Model for Lazarus } \\\text { Shoe Company for } \\\text { January }\end{array} & \text { (All cost in } \$) \\\hline & \\\hline \text { Unit Price } & 47 \\\hline \text { Unit Cost } & 22 \\\hline \text { Fixed Cost for Production } & 350,000 \\\hline \text { Demand } & 40,000 \\\hline & \\\hline \text { Model } & \\\hline & \\\hline \text { Unit Price } & 47 \\\hline \text { Quantity Sold } & 38,000 \\\hline \text { Rev enue } & \\\hline & \\\hline \text { Unit Cost } & 22 \\\hline \text { QuantityProduced } & 38,000 \\\hline \text { Variable Cost } & \\\hline \text { Fixed Cost } & 350,000 \\\hline & \\\hline \text { Profit } & \\\hline\end{array}$ -Calculate the total profit.

Explain how a two-way data table is created.

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 a modern software tool to perform statistical calculations. Using the spreadsheet below to answer the following question(s). The spreadsheet below shows the net income model for a company that sells shoes. A B 1 Net Income Model 2 3 Data 4 5 Sales \ 10,000,000 6 Cost of Goods Sold \ 6,400,000 7 Administrative Expenses \ 500,000 8 SellingExpenses \ 900,000 9 Depreciation Expenses \ 750,000 10 Interest Expenses \ 70,000 11 Taxes \ 620,000 12 13 Model 14 15 Gross Profit \ 3,600,000 16 Operating Expenses \ 2,150,000 17 Net Operating Income \ 1,450,000 18 Eamings Before Taxes \ 1,380,000 19 20 Net Income -Which of the following formulas would be used to calculate the net operating income?

Use the table below to answer the following question(s). 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) cost (\$) } & 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 a modern software tool to perform statistical calculations. Use the table below to answer the following question(s). Below is the profit model spreadsheet for the Lazarus Shoe Company producing their latest model of shoes for the month of January. $\begin{array}{|l|l|}\hline\begin{array}{l}\text { Profit Model for Lazarus } \\\text { Shoe Company for } \\\text { January }\end{array} & \text { (All cost in } \$) \\\hline & \\\hline \text { Unit Price } & 47 \\\hline \text { Unit Cost } & 22 \\\hline \text { Fixed Cost for Production } & 350,000 \\\hline \text { Demand } & 40,000 \\\hline & \\\hline \text { Model } & \\\hline & \\\hline \text { Unit Price } & 47 \\\hline \text { Quantity Sold } & 38,000 \\\hline \text { Rev enue } & \\\hline & \\\hline \text { Unit Cost } & 22 \\\hline \text { QuantityProduced } & 38,000 \\\hline \text { Variable Cost } & \\\hline \text { Fixed Cost } & 350,000 \\\hline & \\\hline \text { Profit } & \\\hline\end{array}$ -Calculate the variable cost of production.

Use the table below to answer the following question(s). 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 Model } & & \\\hline 2 & & & \\\hline 3 & \text { Data } && \begin{array}{l}\text { What-If } \text { Demand } \\\text { Values }\end{array} \\\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}$ -is the term used by Analytic Solver Platform for systematic methods of "what-if" study.

Discuss how overbooking decisions are made by service businesses.