C268 SPREADSHEETS EXAM
QUESTIONS AND ANSWERS 2024
Assume you have made the first 36 payments on your loan. You want to trade the
car in for a new car. You believe that you can sell your car for $4000. Will this
cover the balance remaining on the car in period 36? Answer either "Yes" or "No"
in cell G15 from the drop-down menu. - CORRECT ANSWE✅✅ - No
.Calculate the "Net Profit" portion of the "Profit Summary" section of the model by
subtracting the arena fee from the profit before the arena fee. - CORRECT
ANSWE✅✅ - =C35-C36
.Calculate the commissions paid for each product type. The commissions paid are
calculated as the expected sales for each product type times the commission
percentage from the "Model Inputs" section of the model. - CORRECT
ANSWE✅✅ - =C15*C5
.Calculate the cost of goods sold (COGS) for each product. The COGS is
calculated as the sales for each product type times the COGS percentage from the
"Model Inputs" section of the model for that product type. - CORRECT
ANSWE✅✅ - =C15*C4
.Calculate the gross profit for each product. The gross profit is calculated as the
sales minus the COGS for each product. - CORRECT ANSWE✅✅ - =C15-C16
.Calculate the number of employees that are needed to work the event for each type
of product. This is calculated as the profit before the arena fee for each type of
product, times the employees (% of expected profit) for each product type from the
"Model Inputs" section of the worksheet. Because you cannot hire a fraction of an
employee, round your calculations up to the next whole number. - CORRECT
ANSWE✅✅ - =ROUNDUP(C26*C7,0)
.Calculate the payment amount for the loan in cell C15. Reference the cells
containing the appropriate loan information as the arguments for the function you
use. Cells C20-C67 in the "Payment" column are populated with the payment
, amount from cell C15. [34 Points] - CORRECT ANSWE✅✅ -
=PMT(Rate/#months of term,LoanAmt)
=PMT(C13/12,C12,C11)
.Calculate the profit before the arena fee for each product type. This is the gross
profit minus the total operating expenses for each type of product, including the
salary expenses (yet to be calculated). - CORRECT ANSWE✅✅ - =C17-C24
.Calculate the salary expenses for each product type. This is calculated as the
number of employees needed for each product type times the salary (per employee)
in the "Model Inputs" section. (Employees make the same salary despite the
product type.) This will create a circular reference in your worksheet. You will
need to change the options in Excel to appropriately account for the circular
reference. - CORRECT ANSWE✅✅ - =C28*$C$8
.Calculate the total interest paid in cell G13. The total interest paid is the sum of all
interest paid in the "Interest" column of the amortization table. - CORRECT
ANSWE✅✅ - =SUM(D20:D67)
.Calculate the total operating expenses for each product type. This is the sum of
salary expenses (yet to be calculated), commissions, and the fixed costs for each
product type. - CORRECT ANSWE✅✅ - =SUM(C21,C22,C23)
.Calculate, in cell D20, the interest amount for period 1 by multiplying the balance
in period 0 (cell F19) by the loan interest rate (cell C13) divided by 12. Dividing
the interest rate by 12 results in the monthly interest rate. This formula is reusable.
The interest for a given period is always the monthly interest rate times the balance
from the previous period. - CORRECT ANSWE✅✅ - =F19*$C$13/12
.Calculate, in cell E20, the principal amount for period 1. The principal amount is
the difference between the payment amount (cell C20) and the interest amount
(cell D20) for period 1. Construct your formula in such a way that it can be reused
to complete the "principal" column of the amortization table. - CORRECT
ANSWE✅✅ - =C20-D20
.Calculate, in cell F20, the balance for period 1. The balance is the difference
between the balance for period 0 (cell F19) and the principal amount for period 1
(cell E20). This formula is reusable. The balance is always calculated as the
QUESTIONS AND ANSWERS 2024
Assume you have made the first 36 payments on your loan. You want to trade the
car in for a new car. You believe that you can sell your car for $4000. Will this
cover the balance remaining on the car in period 36? Answer either "Yes" or "No"
in cell G15 from the drop-down menu. - CORRECT ANSWE✅✅ - No
.Calculate the "Net Profit" portion of the "Profit Summary" section of the model by
subtracting the arena fee from the profit before the arena fee. - CORRECT
ANSWE✅✅ - =C35-C36
.Calculate the commissions paid for each product type. The commissions paid are
calculated as the expected sales for each product type times the commission
percentage from the "Model Inputs" section of the model. - CORRECT
ANSWE✅✅ - =C15*C5
.Calculate the cost of goods sold (COGS) for each product. The COGS is
calculated as the sales for each product type times the COGS percentage from the
"Model Inputs" section of the model for that product type. - CORRECT
ANSWE✅✅ - =C15*C4
.Calculate the gross profit for each product. The gross profit is calculated as the
sales minus the COGS for each product. - CORRECT ANSWE✅✅ - =C15-C16
.Calculate the number of employees that are needed to work the event for each type
of product. This is calculated as the profit before the arena fee for each type of
product, times the employees (% of expected profit) for each product type from the
"Model Inputs" section of the worksheet. Because you cannot hire a fraction of an
employee, round your calculations up to the next whole number. - CORRECT
ANSWE✅✅ - =ROUNDUP(C26*C7,0)
.Calculate the payment amount for the loan in cell C15. Reference the cells
containing the appropriate loan information as the arguments for the function you
use. Cells C20-C67 in the "Payment" column are populated with the payment
, amount from cell C15. [34 Points] - CORRECT ANSWE✅✅ -
=PMT(Rate/#months of term,LoanAmt)
=PMT(C13/12,C12,C11)
.Calculate the profit before the arena fee for each product type. This is the gross
profit minus the total operating expenses for each type of product, including the
salary expenses (yet to be calculated). - CORRECT ANSWE✅✅ - =C17-C24
.Calculate the salary expenses for each product type. This is calculated as the
number of employees needed for each product type times the salary (per employee)
in the "Model Inputs" section. (Employees make the same salary despite the
product type.) This will create a circular reference in your worksheet. You will
need to change the options in Excel to appropriately account for the circular
reference. - CORRECT ANSWE✅✅ - =C28*$C$8
.Calculate the total interest paid in cell G13. The total interest paid is the sum of all
interest paid in the "Interest" column of the amortization table. - CORRECT
ANSWE✅✅ - =SUM(D20:D67)
.Calculate the total operating expenses for each product type. This is the sum of
salary expenses (yet to be calculated), commissions, and the fixed costs for each
product type. - CORRECT ANSWE✅✅ - =SUM(C21,C22,C23)
.Calculate, in cell D20, the interest amount for period 1 by multiplying the balance
in period 0 (cell F19) by the loan interest rate (cell C13) divided by 12. Dividing
the interest rate by 12 results in the monthly interest rate. This formula is reusable.
The interest for a given period is always the monthly interest rate times the balance
from the previous period. - CORRECT ANSWE✅✅ - =F19*$C$13/12
.Calculate, in cell E20, the principal amount for period 1. The principal amount is
the difference between the payment amount (cell C20) and the interest amount
(cell D20) for period 1. Construct your formula in such a way that it can be reused
to complete the "principal" column of the amortization table. - CORRECT
ANSWE✅✅ - =C20-D20
.Calculate, in cell F20, the balance for period 1. The balance is the difference
between the balance for period 0 (cell F19) and the principal amount for period 1
(cell E20). This formula is reusable. The balance is always calculated as the
