1. Problem 20 on p. 416 (40 pts in total; 10 pts each): Complete the model formulation (B14 to B17).
Data
Net sales
Cost of sales
Selling expenses
Variable rate (% of net sales)
Administrative expenses
Interest expenses
Tax rate
$1,250,000
$300,000
20. MasterTech is a new software company that devel-
ops and markets productivity software for municipal
government applications. In developing their income
statement, the following formulas are used:
gross profit = net sales \text{ } - \text{ } cost of sales
net operating profit = gross profit \text{ } - \text{ } administrative expenses
\text{ } - \text{ } selling expenses
net income before taxes = net operating profit
\text{ } - \text{ } interest expense
net income = net income before taxes \text{ } - \text{ } taxes
Fixed
$90,000
8%
$50,000
$8,000
50%
Model
a. Gross profit
b. Net operating profit
c. Net income before taxes
d. Net income
Net sales are expected to be $1,250,000. Cost of sales
is estimated to be $300,000. Selling expenses have
a fixed component that is estimated to be $90,000
and a variable component that is estimated to be 8%
of net sales. Administrative expenses are $50,000.
Interest expenses are $8,000. The company is taxed
at a 50% rate. Develop a spreadsheet model to calcu-
late the net income. Design your spreadsheet using
good spreadsheet-engineering principles.
