|
Question 797306: Refining the "What-If-Analysis"
to make a better Decision Support System
Step 1
You should do three things, a) download and save a copy of the 2nd Excel assignment file from the COT Dept's web server, b) make a backup copy of the assignment file in case something goes wrong, and c) write down the original value of cell C50 to compare with the value Solver generates later.
Step 2
Please note: this is a modified version of the spreadsheet used in the first exercise so its starting point and results are not the same as you got in that assignment. That exercise did not make full use of the error rate information and included a multiplier to highlight the contrast between the various possible optimizations.
Step 3
In this exercise we will only use Solver to maximize the 'Rate of Return', but we now need to factor in the effect of marketing and the error rates. With an annual $100,000 base advertising budget 250,000 widgets per year can be sold. For each additional $1,000 spent on advertising you sell 5,000 more widgets per year. Obviously, you cannot sell more than 100% of production, but since there is a faulty production rate which increases as speed of production increases you cannot actually sell 100% of the items produced, see Q1. Insert a new section R for advertizing which includes this marketing effect and then use Solver to find the best 'Rate of Return' result including both marketing and production formulas. Your factory has room for up to 12 employees with their workstations so in Solver you must allow cell C12 (as well as the original variable in C7 and C10) to vary with constraints between 9 and 12 in addition to the previous variable constraints. Do not forget to include the "per Widget" cost of advertising in line U then use Solver to maximize the 'Rate of Return' in line Z.
Step 3a
The following list outlines the formulas and factors you need to account for from the previous paragraph. Please note: the cell C30 shows the factory is only open an average of 22 days per month. Remember, do not just calculate the numbers based on the original spreadsheet because Solver is going to re-calculate these values based on maximizing the 'Rate of Return' so you must add new formulas to the spreadsheet for this to work.
insert several rows between row 37 and the original row 38 to use in which to put your new formulas
$100,000/year advertising sells 250,000 widgets
create a formula to find out much additional advertising to buy at the rate of $1,000 per 5,000 widgets
create a formula to calculate how many salable widget are produced each year, e.g. total widget production minus faulty widgets
create a formula to find out how many salable widgets beyond the base 250,000 the company makes each year
create a formula to find out how much money will the advertising cost to sell these additional widgets
create a formula to calculate the 'per widget' cost of advertising
be sure the 'Advertising per widget' cost is added to the total per widget cost in cell by 'U. total cost:'
Step 4
If you need to, feel free to watch the basic using Solver video linked here to refresh your memory.
Step 5
Solver should already be activated from the last assignment, but if it is not follow these instructions to activate it. In older versions of Excel like those in Office XP or Office 2003, you activate Solver by clicking on the "Tools" drop down menu, select "Add-ins...", then click the check box for "Solver" if it is present. If it is not present and you have MS Office Professional then you can install it from the original MS Office CD.
In Excel from Office 2007 Professional, click the "Data" ribbon and if "Solver" shows in the Analysis group you are ready to go. If not click the "Start Orb" in the upper left corner, click "Excel Options" on the bottom bar of that menu, click "Add-Ins", select "Excel Add-ins" in the Manage dialog at the bottom of that window, then Click "Go". You should see "Solver Add-in" in the available add-ins window then just click the check box for Solver and click OK; now you should see Solver in the Analysis box of the Data ribbon. If Solver is not an available add-in and you have Office 2007 Professional then you can install it from the original MS Office DVD.
If you do not have Office Professional then you will need to use Excel at one of the GBC open computer labs which do have it. You can download a 15-day free trial version of Solver but please be aware that you will need this for about 4 weeks. ---If none of these options work for you, please contact me at once!---
Step 6
After noting the original value of cell C50 start Solver either by clicking "Tools" then select Solver in older Excel versions or in Excel 2007 select the "Data" ribbon, then click "Solver" in the "Analysis" group. I have already entered the previous Solver information including C50 as the cell to be maximized, using cell references C7, C10 in the "By Changing Cells:" as the variables from assignment 1 and also specified the previous "Subject to Constraints:" data with the following:
C7 <= 35
C7 >= 20
C10 <= 10
C10 >= 8
However, you must now add the new variable cell C12 and its constraints of:
C12 <= 12
C12 >= 9
When you have added the new Solver information, click the "Solve" button then click the "OK" button to "Keep Solver Solution" and note the new value for the cell C50 to see the effect of the marketing you included in this new what-if-analysis.
Step 7
Answer the question at the bottom of the spreadsheet.
Step 8
If you have done this correctly then you have calculated the effects of two more factors (marketing and quality control) in this business "what-if-analysis" to improve its use as a decision support system. Once you have completed this work submit it to the WebCampus assignment drop box.
Excel table
Complete a product cost/profit analysis for a manufacturer of widgets from the following data:
The instructions you need to learn to use solver are given in Rows 52-66 below.
Personnel Costs
A. Each worker's base pay is: 9.00 per hour
Each worker can make an average of: 20 widgets per hour
B. Actual production: 20 widgets per hour
Bonus pay per widget per hour: 0.15 cents per hour
Actual hourly rate: $9.00
C1. Shift length 8 in hours
Overtime rate: $13.50
C2. This small manufacturer employs: 9 workers to make widgets
D. Widget production: 1,440 per day (B x C1 x C2)
E. Personnel cost: $0.4500 per widget (A / B)
Equipment Costs
F. Cost of manufacturing equipment: $1,280.00 per workstation
G. Each workstation can make: 36,000 widgets before replacement
# of workstations 9
H. Equipment cost: $0.04 per widget (F / G)
Cost of Materials
I. Cost of raw materials: $3,000.00 per bundle
J. Number of finished widgets: 10,000 per bundle
K. Cost of raw materials: $0.30 per widget (I / J)
Building Costs
L. Lease cost: $2,816.00 per month
M. Cost of utilities: $500.00 per month
N. Work days: 22 per month
O. Building costs: $150.73 per day ((L + M) / N)
P. Building costs: $0.10 per widget (O / D)
Failure rate increases exponentially as production speed increases, see the formula in Q1
Q1. # of faulty widgets produced: 0 per day
Q2. Average costs of a single faulty widget: $5.67 (V * 3)
Q3. Average faulty production costs: $0.00 per perfect widget (Q1 x Q2)/(D - Q1)
Widget Cost Analysis
Personnel cost: $0.45 per widget see E.
Equipment cost: $0.04 per widget see H.
Cost of raw materials: $0.30 per widget see K.
Building costs: $0.10 per widget see P.
Failure rate costs: $0.00 per widget see Q3.
U. Total cost: $0.89 per widget sum E, H, K, P, Q3
V. Price: 1.89 per widget
W. Profit: $1.00 per widget
X. Gross Profit: $380,073.60 per year
Z. Rate of return: 112.3%
What other factors do you think should be included to fully calculate the rate of return?
Answer by solver91311(24713)   (Show Source):
You can put this solution on YOUR website!
This is not a place to get your entire assignment done for you. You should be asking your Finance and Accounting assignment questions on a Finance and Accounting help site. This is Algebra.com.
John
Egw to Beta kai to Sigma
My calculator said it, I believe it, that settles it
|
|
|
| |
