WGU C268 Study Guide for EXCEL PA Updated Version 2023 100% Complete Add a linear trendline to the "Google Revenue Line " chart. - Correct AnswerAdd chart element and trendline and click on linear.
Add a linear trendline to the "Total Google Revenue" chart. - Correct AnswerClick on design and then add chart element. Click on trendline then click on linear.
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 AnswerNo
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 Answer=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 AnswerPrograms=C15*C5 Food=D15*D5 Merchandise=E15*E5
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.term-55 - Correct AnswerPrograms=C15*C4 Food=D15*D4 Merchandise=E15*E4
Calculate the gross profit for each product. The gross profit is calculated as the sales minus the COGS for each product. - Correct AnswerPrograms=C15-C16 Food=D15-D16 Merchandise=E15-E16
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 AnswerPrograms=ROUNDUP(C26*C7,0) Food=ROUNDUP(D26*D7,0) Merchandise=ROUNDUP(E26*E7,0)
- / 2
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. - Correct Answer=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 AnswerPrograms=C17-C24 Food=D17-D24 Merchandise=E17-E24
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 AnswerFile -> Options -> Formulas Under calculation options check the option to "Enable iterative calculation
Programs=C28*C$8 Food=D28*C$8 Merchandise=E28*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
Answer=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 AnswerPrograms=SUM(C21,C22,C23) Food==SUM(D21,D22,D23) Merchandise= =SUM(E21,E22,E23)
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.f - Correct Answer=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 Answer=C20-D20
- / 2