Projected Level of Income and the Fact
Added on - 16 Sep 2019
1After showing the owner, Kevin Jacques, your financial model developed in Part I, he is quite displeasedwith the projected level of income and the fact that the company’s established credit line limit of$100,000 must be exceeded. Jacques would really like the business to earn $120,000 per year.Perform the following analyses and report on your findings.For each calculation, go back to theoriginal assumptions. DO NOT FORGET TO DO THIS.1.This question considers the amount that the selling price would have to increase to earn Jacques’stargeted income of $120,000?To calculate this figure use the GOAL SEEK function inExcel, as follows.To perform a goal seek, go to the top of the spreadsheet and select DATA and then WHAT-IFANALYSIS.From the options in WHAT-IF-ANALYSIS seek GOAL SEEK. You should then see the followingscreen:In “set cell: in the assumptions worksheet click on cell G8 [alternatively, go to Schedule 10 (budgetedincome statement) and click on the net income cell (B14)]
2In “to value”: set net income to $120,000.“By changing cell”: Click the cursor on this window. You want to do thisby changing cell C9of theassumptions worksheet(the selling price). Hit OK after you have entered cell B6 in theassumptions worksheet.The answer will appear in cell C9 of the assumptions worksheet. Now you have learnt a reallyneat tool in excel! Don’t forget it.Question to answer: What percentage increase does this increase in selling price represent? Is itrealistic to raise prices this amount in a competitive market where Capco’s product is notdifferentiated?2.Go back to the original assumptions.Based on his knowledge of the market, the owner is quitesure that the number of units sold cannot be increased unless the sales price is dropped. If the sellingprice was reduced by 2% to $3,577, he believes it is realistic to assume that projected monthly saleswould increase sales substantially from the current estimate of 2 additional units per month He iswondering by how much sales in units would have to increase each month at the lower selling price toearn $120,000 in net income (round your answer to one decimal point). Use GOAL SEEK todetermine whether this suggestion is viable if the company’sproductioncapacityis a total of 750units for the year.3.Continuing on with scenario 2), by how much would net income increase if sales increased only tomatch production capacity at the new (lower) price? [Hint: you need to calculate how much sales inunits can increase each month at the lower selling price to arrive at the production capacity constraintof 750 units by using GOAL SEEK.]4.Under the scenario outlined in 2) above, what is the credit line balance at the end of Decemberassuming there was no production constraint (i.e., you can produce as much as you need)?Explainwhy it has increased so dramatically even though net income has increased substantially (i.e., thecompany has become more profitable)? What do we learn about the currentworking capitalpoliciesof the company?5.Assume the company’s capacity is fixed at 750 caps per year. Based on your analyses in questions1 and 2 above concerning changes to selling prices and volume,where do you think the owner needsto focus to achieve his required profit of $120,000?