Jay Company

I need to design and develop a financial model that arrives at a correct answer/estimation for the specified problem, and supports future decision-making. In addition to solving the problem and creating the model, I need to prepare a spreadsheet/model map that documents use of the model for future users. The project will be graded on approach, execution, Excel skills, presentation, and critical thinking.

Thanks

In Year 1, Jay Company expects to sell 2,243 units at $110 per unit. Sales are expected to increase 25% each year for years 2-4. The unit sales price will remain the same. Labor is 23% of sales, Overhead 10%, Materials 5%, and Variable Sales and Admin, 4%. Fixed Costs, are Factory Overhead (2%) and Sales and Admin (4%). Interest expense of $425 is evenly budgeted over the period. The company tax rate is 20%.

a) Create the Sales Budget for the 4-year period.
b) Calculate the variable cost per unit based on the cost given.
c) Create a budgeted income statement for the 4-year period, using all the information provided and calculated.
d) Calculate the CM ratio, Unit CM, Breakeven in units, Breakeven in dollars for the total 4-year period.
e) If sales remain at the budgeted 4-year level, but fixed costs increase to $367,800, and the company wants to achieve target net income of $
$700,000, recalculate the CM ratio, Unit CM, Breakeven in units, Breakeven in dollars, and Target Breakeven in Units.
f) Create a breakeven chart for Jay Company based on unit increments of 250 and the revised fixed costs of $367,800. (Hint: You want to graph sales, fixed costs and total costs (total variable and fixed) on one scatter plot.)