C268 Spreadsheets
Assume you have got made the first 36 bills in your mortgage. You want to alternate the
automobile in for a brand new vehicle. You believe that you can promote your automobile for
$4000. Will this cowl the balance final on the automobile in length 36? Answer both "Yes" or
"No" in mobile G15 from the drop-down menu. - ANS-No
Calculate the "Net Profit" part of the "Profit Summary" section of the version with the aid of
subtracting the area price from the profit earlier than the area price. - ANS-=C35-C36
Calculate the commissions paid for every product kind. The commissions paid are calculated as
the expected sales for every product kind times the fee percentage from the "Model Inputs"
section of the version. - ANS-=C15*C5
Calculate the fee of products sold (COGS) for each product. The COGS is calculated as the
income for each product kind instances the COGS percentage from the "Model Inputs" segment
of the version for that product kind. - ANS-=C15*C4
Calculate the gross profit for each product. The gross earnings is calculated because the sales
minus the COGS for each product. - ANS-=C15-C16
Calculate the number of employees which can be had to work the event for each type of
product. This is calculated as the earnings earlier than the area price for every kind of product,
times the employees (% of predicted earnings) for every product type from the "Model Inputs"
phase of the worksheet. Because you cannot lease a fraction of an employee, round your
calculations up to the subsequent entire variety. - ANS-=ROUNDUP(C26*C7,zero)
Calculate the charge amount for the mortgage in mobile C15. Reference the cells containing an
appropriate loan records because the arguments for the characteristic you use. Cells C20-C67
within the "Payment" column are populated with the payment amount from mobile C15. [34
Points] - ANS-=PMT(Rate/#months of term,LoanAmt)
=PMT(C13/12,C12,C11)
Calculate the profit earlier than the arena fee for each product kind. This is the gross income
minus the full running charges for every type of product, together with the profits costs (yet to be
calculated). - ANS-=C17-C24
Calculate the earnings charges for every product kind. This is calculated because the variety of
employees wished for each product kind instances the revenue (consistent with employee) in
the "Model Inputs" segment. (Employees make the equal earnings despite the product kind.)
, This will create a round reference on your worksheet. You will need to alternate the options in
Excel to as it should be account for the circular reference. - ANS-=C28*$C$eight
Calculate the full hobby paid in cellular G13. The general interest paid is the sum of all hobby
paid inside the "Interest" column of the amortization desk. - ANS-=SUM(D20:D67)
Calculate the entire operating expenses for every product kind. This is the sum of income costs
(but to be calculated), commissions, and the constant expenses for every product type. -
ANS-=SUM(C21,C22,C23)
Calculate, in cellular D20, the interest quantity for duration 1 through multiplying the stability in
duration 0 (cellular F19) through the loan hobby rate (cellular C13) divided by means of 12.
Dividing the hobby fee by using 12 results inside the monthly hobby rate. This system is
reusable. The interest for a given period is always the monthly interest fee instances the
balance from the previous duration. - ANS-=F19*$C$13/12
Calculate, in cell E20, the fundamental quantity for period 1. The major amount is the distinction
between the fee quantity (mobile C20) and the hobby amount (mobile D20) for duration 1.
Construct your formulation in any such manner that it could be reused to finish the "primary"
column of the amortization desk. - ANS-=C20-D20
Calculate, in cellular F20, the balance for length 1. The balance is the distinction among the
balance for period zero (cellular F19) and the primary amount for duration 1 (cellular E20). This
formula is reusable. The balance is constantly calculated as the difference between the stability
from the previous duration and the principal quantity for the modern length. - ANS-=F19-E20
Calculate, in mobile G12, the entire amount paid via multiplying the fee amount (mobile C15)
with the aid of the time period of the loan (cellular C12). - ANS-=C15*C12
Check to peer if the full hobby calculation inside the amortization desk is correct. The total
interest paid is likewise identical to the difference between the whole quantity paid over the
direction of the loan and the authentic mortgage amount. Insert a system into cellular G14 to
calculate the difference among the whole quantity paid and the unique loan quantity. Notice the
bad sign associated with the unique loan quantity. This fee ought to equal the entire hobby
calculated the use of the amortization desk. - ANS-=G12-ABS(C11)
Complete the "Arena Fee" part of the "Profit Summary" segment of the model with the aid of
referencing the area fee from the "Model Inputs" phase of the worksheet. - ANS-=C9
Complete the "Gross Profit" portion of the "Profit Summary" section of the worksheet via totaling
the gross earnings for all products from the "Gross Profit" phase of the model. -
ANS-=SUM(C17:E17)
Assume you have got made the first 36 bills in your mortgage. You want to alternate the
automobile in for a brand new vehicle. You believe that you can promote your automobile for
$4000. Will this cowl the balance final on the automobile in length 36? Answer both "Yes" or
"No" in mobile G15 from the drop-down menu. - ANS-No
Calculate the "Net Profit" part of the "Profit Summary" section of the version with the aid of
subtracting the area price from the profit earlier than the area price. - ANS-=C35-C36
Calculate the commissions paid for every product kind. The commissions paid are calculated as
the expected sales for every product kind times the fee percentage from the "Model Inputs"
section of the version. - ANS-=C15*C5
Calculate the fee of products sold (COGS) for each product. The COGS is calculated as the
income for each product kind instances the COGS percentage from the "Model Inputs" segment
of the version for that product kind. - ANS-=C15*C4
Calculate the gross profit for each product. The gross earnings is calculated because the sales
minus the COGS for each product. - ANS-=C15-C16
Calculate the number of employees which can be had to work the event for each type of
product. This is calculated as the earnings earlier than the area price for every kind of product,
times the employees (% of predicted earnings) for every product type from the "Model Inputs"
phase of the worksheet. Because you cannot lease a fraction of an employee, round your
calculations up to the subsequent entire variety. - ANS-=ROUNDUP(C26*C7,zero)
Calculate the charge amount for the mortgage in mobile C15. Reference the cells containing an
appropriate loan records because the arguments for the characteristic you use. Cells C20-C67
within the "Payment" column are populated with the payment amount from mobile C15. [34
Points] - ANS-=PMT(Rate/#months of term,LoanAmt)
=PMT(C13/12,C12,C11)
Calculate the profit earlier than the arena fee for each product kind. This is the gross income
minus the full running charges for every type of product, together with the profits costs (yet to be
calculated). - ANS-=C17-C24
Calculate the earnings charges for every product kind. This is calculated because the variety of
employees wished for each product kind instances the revenue (consistent with employee) in
the "Model Inputs" segment. (Employees make the equal earnings despite the product kind.)
, This will create a round reference on your worksheet. You will need to alternate the options in
Excel to as it should be account for the circular reference. - ANS-=C28*$C$eight
Calculate the full hobby paid in cellular G13. The general interest paid is the sum of all hobby
paid inside the "Interest" column of the amortization desk. - ANS-=SUM(D20:D67)
Calculate the entire operating expenses for every product kind. This is the sum of income costs
(but to be calculated), commissions, and the constant expenses for every product type. -
ANS-=SUM(C21,C22,C23)
Calculate, in cellular D20, the interest quantity for duration 1 through multiplying the stability in
duration 0 (cellular F19) through the loan hobby rate (cellular C13) divided by means of 12.
Dividing the hobby fee by using 12 results inside the monthly hobby rate. This system is
reusable. The interest for a given period is always the monthly interest fee instances the
balance from the previous duration. - ANS-=F19*$C$13/12
Calculate, in cell E20, the fundamental quantity for period 1. The major amount is the distinction
between the fee quantity (mobile C20) and the hobby amount (mobile D20) for duration 1.
Construct your formulation in any such manner that it could be reused to finish the "primary"
column of the amortization desk. - ANS-=C20-D20
Calculate, in cellular F20, the balance for length 1. The balance is the distinction among the
balance for period zero (cellular F19) and the primary amount for duration 1 (cellular E20). This
formula is reusable. The balance is constantly calculated as the difference between the stability
from the previous duration and the principal quantity for the modern length. - ANS-=F19-E20
Calculate, in mobile G12, the entire amount paid via multiplying the fee amount (mobile C15)
with the aid of the time period of the loan (cellular C12). - ANS-=C15*C12
Check to peer if the full hobby calculation inside the amortization desk is correct. The total
interest paid is likewise identical to the difference between the whole quantity paid over the
direction of the loan and the authentic mortgage amount. Insert a system into cellular G14 to
calculate the difference among the whole quantity paid and the unique loan quantity. Notice the
bad sign associated with the unique loan quantity. This fee ought to equal the entire hobby
calculated the use of the amortization desk. - ANS-=G12-ABS(C11)
Complete the "Arena Fee" part of the "Profit Summary" segment of the model with the aid of
referencing the area fee from the "Model Inputs" phase of the worksheet. - ANS-=C9
Complete the "Gross Profit" portion of the "Profit Summary" section of the worksheet via totaling
the gross earnings for all products from the "Gross Profit" phase of the model. -
ANS-=SUM(C17:E17)
