highest percentage proportion in the mix leads to high returns, accounting homework help

User Generated

mnvanu

Business Finance

Description

I want you do homework excel

you must do in excel in attachment and instruction in PDF

Unformatted Attachment Preview

Loan Amount Term In Months Interest Rate Payment #NUM! =-PMT(B3/12,B2,B1) Revenue Cost Profit Fizzy Pop Diet Fizz $3.50 $3.85 $1.65 $1.80 Units To Sell Percentage Sales Profit per Product Type 10000 Total Profit $0.00 50% #N/A #N/A #N/A #N/A 50% =B8+C8 Assignment 12 This week’s assignment is going to look at using the goal seek and scenario manager to solve some problems. Begin by downloading, saving, and opening the Assignment 12 Original File from the Canvas Assignment. Problem 1) Click on the Problem 1 worksheet. We are going to use goal seek to figure out what kind of interest rate we need to be able to afford the house we want to buy. Assume that the house is going to cost $175,000 and you plan on taking out a 30 year mortgage. You cannot afford a monthly payment any higher than $950.00. Using goal seek to determine the interest rate, what must the interest rate be in order to achieve a payment of $950? Hint: I have already typed in the PMT formula in Cell B4, so don’t replace it by typing in the value of $950 – you need to use goal seek to handle this. Problem 2) Click on the Problem 2 worksheet – it will be blank since you need to create the entire solution to learn how to set up goal seek type problems. This goal seek problem is going to ask you to figure out the breakeven point on a product, e.g. where the revenue = costs. You are thinking about selling a new SVSU T-Shirt to promote the awesome CS 150 class. You plan on selling the shirt for $14.00. Each shirt costs $12 to make (think of this as a variable cost dependent on the number of shirts made). In addition there is an $1800 cost to set up the printing regardless of how many shirts are printed (think of this as a fixed cost independent of the number of shirts made). Set up a spreadsheet that shows the two cost components and the revenue component. Then set up a blank cell for the number of units that must be sold (the goal seek will fill this number in). Finally you will need to set up a profit formula that determines how much money is made or lost based on the value in the units cell. This isn’t that hard just think about it. *** IMPORTANT *** So how can I tell if you’ve actually used the goal seek or not to solve the problem in Problems 1 and 2 since just a number is brought back? You’re going to need to do two things for each problem. First of all, in Column C, use FORMULATEXT to show me any formulas that are in Column B – there should be one for each problem. Next, go ahead and solve the problem using Goal Seek. Once you have the right answer with goal seek, start to run Goal Seek again up to the point where you have entered the target cell, target value and changing cell with the correct information. Don’t hit the OK button yet – instead take a “screen shot” of the Goal Seek window. See the video in this module, where I demonstrate two methods to do this. You’ll make a screen shot for both Problem 1 and Problem 2. Problem 3) Click on the Problem 3 worksheet. Your company ColaKing makes is introducing two different kinds of soda: Fizzy Pop and Diet Fizz. No more than 10000 units total can be produced of the two products. The VP of Marketing is trying to best determine the product mix that will bring the most profit to the company. An entire advertising campaign will be set up around this mix. The company charges $3.50 for a can of Fizzy Pop and it costs $1.65 to produce. Diet Fizz sells for $3.85 a can and costs $1.80 to produce. Write the formulas in cells B4 and C4 to determine the profit on each can. Don’t worry about the #N/As in Columns D and E – they will go away once you get the formulas added – they just show the formulas you enter using FORMULATEXT. Notice that the initial product mix being considered will be 50/50 – 50% of the cans will be Fizzy Pop and 50% of the cans will be Diet Fizz. In cells B8 and C8 write the formulas to determine the profit on each product line respectively. Cell B10 totals the two profit values into a grand total (I wrote this formula for you – see I help out). Start up the scenario manager and create the following scenarios (the percentage cells will be the ones you want to change): 50/50 40/60 60/40 30/70 70/30 Once you have all of the scenarios in, use the scenario manager to create a summary on its own worksheet (remember that the total profit will be the output cell that you want to see when asked). Based on the scenario summary, which is the best mix to maximize profit? Answer this on the Problem 3 sheet (just type in some text). Does this answer make sense to you as to why it is the best? I want more than a Yes or No here – why is the best mix, really the best? Make printouts to PDF for each of the problems, and upload both your PDF and xlsx solutions to the Canvas assignment.
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hi there. Find the solutions attached. Solved expertly.

Loan Amount
175,000
Term In Months
360
Interest Rate
0.050984999
Payment
$950.00 =-PMT(B3/12,B2,B1)

From the question, Loan needed is 175,000
Term is 30*12=360 days

Procedure
1. Select the cell containing the formula. (B4)
2. From Data tab, click what-if analysis and and select Go-s
3. From dialog box, -fill 'Set cell' with sele...


Anonymous
Super useful! Studypool never disappoints.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags