Design a spreadsheet to compute the expected net income from sales for each quarter and for the whole year using the assumptions below. Your manager wants to be able to change any of the assumptions and automatically compute the new expected net income.
The sales department has estimated sales unit of 1000 for the first quarter and a 5% increase each succeeding quarter. The sales department plans to sell the product for $18.95 each. Manufacturing has developed an estimate for the cost of goods sold as $12.45 per unit. The advertising department has a tentative contract for $1,000.00 per quarter.
The variables (assumptions) to be considered are: first quarter sales, quarterly sales percent increase, selling price (each), cost of goods sold (each), and the quarterly advertising expenses.
Notes: Net income = gross income – advertising expense
Gross income = revenue – cost of goods sold
Revenue = unit sales × selling price