Business finance - accounting assignment | Accounting homework help
- Bibliography. Include appropriate references and follow APA style.
Initial Utilizing Excel’s SOLVER Function – Tutorial The first thing you need to do in order to run the SOLVER is to activate it under the “Add-Ins” tab. Here is how to activate the SOLVER function. STEP ONE – Click on “File” at the top menu STEP TWO – Click on “Options” at the very bottom of the left-hand side. STEP THREE – Click on “Add-Ins” STEP FOUR – Click on “Go” STEP FIVE – Select the checkbox which says “Solver Add-In” and hit OK STEP SIX – Make sure the SOLVER option is not available under the “Data” tab at the top. Now that you have the SOLVER installed in your version of Excel, you can begin preparing the spreadsheet so that you can use the SOLVER. There are several formulas which need to be in place for the SOLVER to work because it references various cells. You need to refer back to Chapter 2 to understand each type of cell and how it works (e.g., data cells, objective cell, etc.). The steps to using the SOLVER are presented in Chapter 2.5 and 2.6. In addition to Chapter 2, you should read Chapter 5 on the What-If Analysis because the advertising scenario in the case utilized those principles. To prepare the spreadsheet, you should begin by creating a few formulas as follows: STEP ONE – Compute the formula for Cells B8 through I8. If you know the selling price for one Table Lamp is $120 and the cost to produce that same Table Lamp is $66 for material and $16 for production, then you know the unit profit (Cell B8) per one Table Lamp. $120 – ($66 + $16) = Unit Profit
In Excel, the formula for the above equation will be:
=B5-(B6+B7)
Do the same type of equation for the remaining cells C8 through I8. STEP TWO – Compute the formulas for the LHS under Capacity Constraints (Cells B23 -26) When you downloaded the original data file from Canvas, you will notice there is a formula already inserted for the LHS for Cell B23. The capacity constraint refers to the maximum possible number of units the factory/department can make for regular time or overtime. In Chapter 3 and 5 of the textbook, you will notice they suggested to put a less than or equal to sign in between the LHS and RHS as a reminder that the total number produced by the factors/department cannot exceed the maximum on the RHS. To calculate the remaining values for cells B24 through B26, you simply need to do a sum of the number of regular and overtime used for each department. For example, we want the number of overtime Table Lamps plus the number of overtime Floor Lamps to get the total number of overtime units for department 1. Therefore, B24 will be as follows:
=C13+E13
Because there are no values in the Units Produced cells (bright yellow) just yet because we have not run the SOLVER, these cells will reflect zero (0). Use the same logic to type the formulas for the remaining cells. STEP THREE - Compute the formulas for the LHS under Demand Constraints (Cells B28 – 31) You all know the basics of supply and demand, and that is what this constraint is illustrating. If you are a production company, you do not want to produce more than you can sell because you're basically wasting your time and money. With that said, the demand constraint represents about how many of each product you anticipate being able to sell in the given amount of time. Therefore, the total Table Lamps produced regular and overtime should not exceed the given value of 60,000 or you will be wasting time and money. The formula you need for cells B28 through B31 should reflect this assumption. The total number of Table Lamps (regular and overtime) should not exceed 60,000 units. Here is how this would be reflected in the formula for cell B28:
=B13+C13
Use the same logic to type the formulas for the remaining cells. STEP FOUR – Preparing the formula for the Objective Function (Cell B19) To compute a total profit for the particular mix of product, you would need to multiply the unit profit by the number of units sold. Right now we do not know the optimal number of units produced, but we can still create the formula which would compute the total profit. The SOLVER function will be telling us how many units are optimal for each product in the next couple of steps.
To calculate the total profit, you need to know the following:
Total Profit = (Table Lamp Unit Profit X Number of Table Lamps) + (Floor Lamp Unit Profit X Number of Floor Lamps) + (Ceiling Lamp Unit Profit X Number of Ceiling Lamps) + (Pendant Lamp Unit Profit X Number of Pendant Lamps) While you could literally type the above formula in Cell B19, there is an easier way because we are basically computing what is called a SUMPRODUCT. Instead of the long formula above, you can use:
=SUMPRODUCT(B8:I8,B13:I13)
When you type this formula, it will give you a result of zero (0) because there is nothing in the Units Produced cells (Bright Yellow – Cells B13 to I13) As Dr. Yurova explained in the chat last evening, you need to subtract the advertising costs to achieve the net total profit as follows:
=SUMPRODUCT(B8:I8,B13:I13)-SUM(B15:E15)
STEP FIVE – Put your cursor on the Objective Function cell (Orange) and click on the SOLVER function (refer to STEP SIX above).
Double check that the settings of the SOLVER are as follows:
Make sure to select “Sensitivity” before you click “OK” Refer to Chapter 3 and 5 to interpret the sensitivity report. Hope this tutorial has helped you with the Excel SOLVER portion of the case. image7.png image8.png image9.png image1.png image2.png image3.png image4.png image5.png image6.png
- Explain the ethical approach concerning means and ends that you would apply if you had a role as the chief of police in your hometown.
- research company
- Strategic Plan:
- BUS644 Operations Management / Outsourcing
- Evaluating Academic Research Reports
- Midterm
- Marketing Ethics Pink Slime
- Genetic Drift*****A++ Rated Tutorial Already***** Use as a Guide Paper*****
- Need this done!
- Vector analysis homework