In cell H6, enter a formula to determine any discount that should be applied. If the payment method was Express Miles or Rewards, the customer should receive the discount shown in B42. If no discount should be applied, the formula should return a zero. Use the named range for cell B42, not the cell address, in this formula
The Excel cells are given as follows:
E5 is no. of days rented
F5 is Payment Method
G5 is Daily Rate
H5 is Total
E6 is 3
F6 is Credit Card
G6 is $49.99 Cell B42 is 20% (which I named the cell Discount)
In cell H6, enter a formula to determine any discount that should be applied. If the payment method was Express Miles or Rewards, the customer should receive the discount shown in B42. If no discount should be applied, the formula should return a zero. Use the named range for cell B42, not the cell address, in this formula
The Excel cells are given as follows:
E5 is no. of days rented
F5 is Payment Method
G5 is Daily Rate
H5 is Total
E6 is 3
F6 is Credit Card
G6 is $49.99 Cell B42 is 20% (which I named the cell Discount)
Related questions
You are an account manager for Inland Jewelers, a regional company that makes custom class rings for graduating seniors. Your supervisor requested a workbook to report on new accounts created on payment plans. The report should provide details on total costs to the student as well as payment information. Each ring financed has a base price that can fluctuate based on ring personalization.
Insert Current Date
You open the starting workbook you previously created, and insert the current date and time.
Open the e02c1ClassRing workbook, and then save it as e02c1ClassRing_LastFirst.
Insert a function in cell B2 to display the current date and format as a Long Date.
Set column Bâs width to Autofit.
Calculate Cost
You are ready to calculate the cost of each class ring ordered. The rings are priced based on their base metal as displayed in the range A15:B19.
Insert a lookup function in cell C5 to display the ring cost for the first student.
Copy the function from cell C5 down through C11 to complete column C.
Apply Accounting Number Format to column C.
Determine the Total Due
You will calculate the total due for each studentâs order. The total is the base price of the ring plus an additional charge for personalization if applicable.
Insert an IF function in cell E5 to calculate the total due. If the student has chosen to personalize the ring, there is an additional charge of 5% located in cell B21 that must be applied; if not, the student pays only the base price. Use appropriate relative and absolute cell references.
Copy the function from cell E5 down through E11 to complete column E.
Apply Accounting Number Format to column E.
Calculate the Monthly Payment
Your next step is to calculate the periodic payment for each studentâs account. The payments are based on the years financed in column F and the annual interest rate in cell B22. All accounts are paid on a monthly basis.
Insert the function in cell G5 to calculate the first studentâs monthly payment, using appropriate relative and absolute cell references.
Copy the formula down the column.
Apply Accounting Number Format to column G.
Finalize the Workbook
You perform some basic statistical calculations and finalize the workbook with formatting and page setup options.
Calculate totals in cells C12, E12, and G12.
Apply Accounting Number Format to the cells C12, E12, and G12.
Set 0.3" left and right margins and ensure that the page prints on only one page.
Insert a footer with your name on the left side, the sheet name in the center, and the file name on the right side.
Save and close the workbook. Based on your instructorâs directions, submit e02c1ClassRing_LastFirst.
MUST BE DONE IN EXCEL
| Inland Jewelers | ||||||
| Date | ||||||
| Name | Ring Type | Cost | Personalized | Total | Years | Monthly Payment |
| Dodson | Silver | Yes | 1 | |||
| Jones | Gold | Yes | 1 | |||
| Clarke | Titanium | No | 2 | |||
| Ranger | Platinum | No | 3 | |||
| McKinley | Platinum | Yes | 3 | |||
| Broadnax | Gold | No | 2 | |||
| Burch | Titanium | Yes | 3 | |||
| Totals | ||||||
| Ring Type | Cost | |||||
| Silver | $400.00 | |||||
| Gold | $550.00 | |||||
| Titanium | $700.00 | |||||
| Platinum | $750.00 | |||||
| Personalizing Surcharge | 5% | |||||
| Interest Rate | 3% | |||||
You have been hired as an accountantfor B&G Co., a corporation performing diverse consultingservices in Detroit, Michigan. B&G Co. prepares financialstatements on monthly bases.
ProjectScope: You are to record the transactions forDecember, prepare the monthly adjustments, and prepare thefinancial statements using the Excel workbook provided. Then youwill close the fiscal year and prepare the books for next year.
Directions: The assignment encompasses twofiles: Directions and Transactions (this Word document)and Forms (a separate Excel workbook). Your solutionshould be worked in Excel and the completed Excel workbooksubmitted for grading.
You should use Excel formula whereappropriate and cell references to carry forward values and numbersbetween worksheets within the workbook. Simply typing values inExcel will result in a reduced score, even if the correctsolution is provided. You should use formula whereverpossible.
Analyze the narrative to prepare and record the transactions forDecember to the General Journal.
Post the journal entries to ledger accounts using T-accounts(Donât forget the opening balances from the information given onthe post-closing trial balance on Nov. 30th.)
Prepare Income Statement and the Retained Earnings Statement forthe month ended December 31, 2016 and Balance Sheet as of December31, 2016 in good forms. Use the multi-step format for the IncomeStatement.
Close the temporary accounts, posting any net income or loss toretained earnings.
Due Date: Dec.8th.
Transactionsin December 2016:
Dec. | 1 | The equipment was completely destroyed by the regional earthquake. âLoss by earthquakeâ was recognized. |
1 | Lent cash to another company and received a 2 year, $20,000zero-interest-bearing note. The market rate of interest for a noteof similar risk is 9 percent. | |
1 | Purchased new equipment that costs $12,000 and issued 1,000shares of common stock (no-par stock) to the equipment seller. | |
3 | Cash payment on accounts payable amounted to $6,000. | |
4 | Sold land for $13,000 cash. | |
10 | Collected $15,000 as payment for amounts previously billed(suppose the payment was made within the discount period). | |
13 | One of the customers went bankrupt. B&G wrote off $2,000account receivable. | |
15 | Paid monthly salaries of $20,000 to employees | |
16 | Issued 1,000 shares of preferred stock at $10 per share | |
17 | Purchased 500 shares of ABC corporationâs common stock at $15(per share) and classify the securities as available-for-salessecurities | |
20 | Found that the company incorrectly overstated its Novemberaccount receivable and sales revenue by $1,000 and made a journalentry to correct the error. | |
29 | Performed services for a customer for $30,000 cash | |
30 | Performed $30,000 services on account with the following terms:2/15, n/30. B&G records credit sales using the net method | |
31 | Dividends of $5,000 were declared and paid. $ 1,025 is paid topreferred stockholders and the rest is paid to the common stockholders. | |
31 | ABC corporation declared $ 5 dividend per share (to common stockholders). It will be paid in 2017. |
* Additional information
1. Ignoretax effect.
2. Salariesexpenses incurred but not paid prior to Dec. 31sttotaled $10,500.
3. Thecompany received the bill for utility services (electricity) thatthe firm used during December in the amount of $6,000. The companywill pay the bill in Jan. 2016.
4. $3,000 ofsupplies remained at the end of December.
5. Thecompany use the âAllowanceâ method for possible accounts receivablewrite-offs in the future and estimated that 5% of the outstandingaccount receivable will not be collected.
6. The equipmentpurchased on Dec. 1st depreciates $200 per month.
7. Thecommon stock price of ABC Corporation on December 31st is still $15 per share.
