Projected Level of Income and the Fact
5 Pages1575 Words197 Views
Added on 2019-09-16
Projected Level of Income and the Fact
Added on 2019-09-16
ShareRelated Documents
1After showing the owner, Kevin Jacques, your financial model developed in Part I, he is quite displeased with 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 the original assumptions. DO NOT FORGET TO DO THIS.1.This question considers the amount that the selling price would have to increase to earn Jacques’s targeted income of $120,000? To calculate this figure use the GOAL SEEK function in Excel, as follows.To perform a goal seek, go to the top of the spreadsheet and select DATA and then WHAT-IF ANALYSIS.From the options in WHAT-IF-ANALYSIS seek GOAL SEEK. You should then see the following screen: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 this by changing cell C9 of theassumptions worksheet (the selling price). Hit OK after you have entered cell B6 in the assumptions worksheet.The answer will appear in cell C9 of the assumptions worksheet. Now you have learnt a really neat tool in excel! Don’t forget it.Question to answer: What percentage increase does this increase in selling price represent? Is it realistic to raise prices this amount in a competitive market where Capco’s product is not differentiated? 2.Go back to the original assumptions. Based on his knowledge of the market, the owner is quite sure that the number of units sold cannot be increased unless the sales price is dropped. If the selling price was reduced by 2% to $3,577, he believes it is realistic to assume that projected monthly sales would increase sales substantially from the current estimate of 2 additional units per month He is wondering 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 to determine whether this suggestion is viable if the company’s productioncapacity is a total of 750 units for the year. 3.Continuing on with scenario 2), by how much would net income increase if sales increased only to match production capacity at the new (lower) price? [Hint: you need to calculate how much sales in units can increase each month at the lower selling price to arrive at the production capacity constraint of 750 units by using GOAL SEEK.] 4.Under the scenario outlined in 2) above, what is the credit line balance at the end of December assuming 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., the company has become more profitable)? What do we learn about the current working capital policiesof 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 needs to focus to achieve his required profit of $120,000?
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
CapCo Financial Analysislg...
|6
|1489
|172
Balance Sheet Analysis Assignmentlg...
|6
|1489
|298
Evaluation of Options for Maximum Cash Flowslg...
|4
|1466
|88