Exam 11: Spreadsheet Modeling and Analysis

Use the table below to answer the following question(s). 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. Retirement 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.

In Excel's Analytic Solver Platform, a parameter is a set of outputs in a model.

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 revenue for units sold.

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

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 income value using only the data value?

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

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 annual revenue for the fourth year.

Use the table below to answer the following question(s). 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. Retirement 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?

How can managers judge the validity of a model?

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

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

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

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 -What is the average daily sale during the full retail sales period?

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 income value using only the information in the Model, and not in the Data section?

How does a tornado chart help make sense of inputs in analyzing data and models?

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

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 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 revenue during the full retail sales period.

Calculate the economic value of a loyal customer for a company given that the customer purchases, on an average, worth $43worth 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 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}$ -From the "what if" values, calculate the total cost when demand is 40,000.