Reference no: EM132413149

Problems -

Prob1) The model to the right is a very simple Income/Loss Statement for a manufacturing business. There are many simplifications in this model, but an Income/Loss Statement is a very useful tool for analyzing profitability when numerous variables need to be considered. The model assumes that the variable values are known (deterministic), but this does not restrict the user from asking numerous important "What-If" questions. You can determine the model's assumptions by examining the cells where calculations take place...e.g. notice the parameters in Column N used in the calculations.

a) Build a two-way data table that varies Var. Cost % from 0.05-0.40 (in increments of 0.05 on the row) and Revenue from 250,000-1,250,000 (in increments of 50,000 on the column). Add conditional formatting to the data table that indicates when a value is negative.

b) Add a form-control scroll bar to control Tax Rate, which should vary from 0.24 to 0.45 in 0.01 unit increments. (Remember that the values of form control are integers and between 0-30,000, so you will need a dummy value cell to adjust for the scale.)

Prob2) Use Excel to perform the following tasks on the Income Statement model provided:

a) Create a two-way data table for net income by varying: 1) Gross Profit (Low = 100,000, High = 700,000, Base_Case = 400,000), and 2) Var.Cost% (Low = 0.10, High = 0.30, Base_Case = 0.20). Use conditional formatting to identify negative values (Loss) of Net Income.

b) Create a chart with sensitivity analysis that graphically represents the data table from (a).

c) Create a histogram of the results of a). What are your conclusions?

Prob3) The model to the right, although more complex than the models in Problems 1 and 2, is a very simple Income/Loss Statement for a manufacturing business. There are many simplifications in this model, but it is a very useful tool for analyzing profitability when numerous variables need to be considered. The model assumes that the variable values are known (deterministic), but this does not restrict us from asking numerous important "What-If" questions.

a) Build the model to calculate net income in the cells provided. (Note the various tax rates for EBT.)

b) Create a one-way data table of Sales Units vs. Net Income. Start sales units at 10,000 and end at 20,000, and increase in increments of 1,000.

c) Graph the data table information with a line graph that performs a break-even analysis. "Eye-Ball" the number of sales units that occurs at breakeven.

d) Use Goal Seek to find the exact value of sales units at breakeven.

Prob4) You are an analyst for the Port of Freeport (POF), TX. Your boss, Velma Pham, asks you to do an analysis of the number of hours needed to unload anticipated shipments of coffee on a daily basis. The POF has decided to compete with the Port of New Orleans, LA, which is the No. 2 port for the importation of coffee. Velma has spoken with Hamburg Sud (a large shipping firm with routes in Latin America) that has routes from Colombia and could make Freeport a port of call. These shipments are relatively uncertain, but there is data related to their uncertainty. Two types of containers are used in shipping coffee-ventilated and unventilated-and they are handled in different areas of the port. Treat the ship calls of the two types of containers as independent of one another; that is, the arrival of one type of container ship has no relationship to the arrival of the other.

- Number of ventilated containers arriving on a container ship: normally distributed with a mean of 10 and a standard deviation of 2.5-N(10,2.5).

- Ship calls of ventilated containers arriving per week: a uniform distribution of 0 to 2.

- Unloading hours for each ventilated container: normally distributed with a mean of 12 minutes and standard deviation 2-N(12,2).

- Unventilated containers arriving on a container ship: Normally Distributed with a mean of 11 and a standard deviation of 3-N(11,3).

- Ship calls of unventilated containers arriving per week: a uniform distribution of 0 to 2.

- Unloading hours for each unventilated container: Normally distributed with mean of 9 minutes and standard deviation 1.5-N(9,1.5).

- The number of all types of ship calls (given the information above) is a minimum of 0 (0+0) and a maximum of 4 (2+2) during a week.

a) Velma would like for you to build a model that estimates the operation of a week of arrivals and the related number of hours of unloading required. Additionally, she wants you conduct an experiment in which you replicate the model that you have constructed for that week 1000 times.

b) Provide summary statistics for the replications of the experiment-mean, stdev.s (sample standard deviation), max, min, and median. Also, create a frequency distribution for unloading hours (a Risk Profile) with increments of 20 hrs.

c) What is the approximate probability of there being zero unloading hours in a week?

d) What is the probability of there being 400 or more unloading hours? (Hint: use the frequency distribution created for the risk profile. Create a column of Cumulative Frequency %-Cumulative Frequency/1000, or you can simply use a countif[].)

Prob5) Your small biotech firm operates a fleet of two specialized delivery vans in Chicago. As a policy, your firm has decided that the operational life of a van is 3 years (a cycle), and both vans are purchased at the same time to receive discounted fleet pricing. The driving demands placed on the vans are uncertain, as are the maintenance costs, and each van is different in its use, demand, and costs. In the past, the firm has been surprised by unexpectedly high (and low) maintenance costs associated with the vans; thus, it is important to analyze the potential of cost variation and to use this information in the annual-budgeting process. You decide to model the arrival of failures (breakdowns of the van) that lead to maintenance costs-each failure has a cost.

You and your staff decide that the model should be simple, but that it should reflect reality. The model should also determine the variation in maintenance costs for 3-year cycles of vehicle use. To determine maintenance cost, you assume the following: 1) Miles Demand for each van is randomly selected from a defined probability distribution (Table 1) for each year of operation; thus, 3 Miles Demand (one for each year) for each van in a cycle. 2) Once the Miles Demand is known, a Yearly Failure Rate is determined (Table 2). This is a Poisson-average yearly arrival rate and a Poisson distribution with this arrival rate is then sampled to determine Actual number of Failures. 3) Each failure arrival is assigned a randomly selected cost from a set of normally distributed costs (Table 3). Finally, costs are aggregated for all vans over the 3 year cycle (an experiment) and many trials are simulated to create a risk profile for total 3-year maintenance cost.

a) Create a Monte Carlo simulation that simulates the 3-year cost of maintenance for the fleet. A suggested structure is provided to simplify your efforts. Simulate 5000 trials (experiments).

b) Provide the risk profile for the model in (a), along with the summary statistics-mean, standard deviation, and 5th and 95th percentile.

c) Calculate the 95% confidence interval for the mean of the simulation.

d) What is the value ($ reduction in cost) that you would derive if you could reduce the Yrly Fail-Rate by 1 for all Miles Demand for Van 1, through a preventative maintenance program? For example, in table 2 the rate for 25000 would change to 1, the rate for 40000 would change to 2, etc. Produce the new Risk Profile and determine the new summary stats.

e) How much would you budget for the 3-year maintenance cycle to meet up to 90% of the maintenance costs? (Only consider the results prior to part d).)

Attachment:- Assignment File - Workbook.rar