Acct 307 Excel Project
The robust improves enact facts from a tabulateification transmutation that occurred freshly in the Sigma Bank. The Sigma Bank freshly converted one of its tabulateifications from Classification Square 1 (SQ1) to CAP. You are assured that SQ1 facts is adequate and deferential. The facts nativity in SQ1 is relishnessn in Excel, while the facts from CAP is relishnessn in the melody improve. You, as an accountant in the Sigma Bank, are planning to secure the adequateness and atonement of the fresh tabulateification transmutation to secure the facts in CAP mirrors SQ1. To instrument your retrospect, you are going to constitute a pacification improve in Excel. In a nutshell, the pacification improve is adapted to conduct the adequateness and atonement of facts in twain tabulateifications. In this contingency, you are demonstrating the adequateness and atonement of the hypothecations as well-mannered-mannered-behaved-behaved as their attainments through a pacification improve.
There are multiple hypothecations in the SQ1 tabulateifications, which were converted to the new tabulateification -CAP. Each hypothecation has diverse attainments, such as hypothecation reckon, highest, manliness time, etc. Your aim is to realize the transmutation adequateness and atonement of the hypothecations, including unfailing key attainments. To do so, you are asked to realize the forthcoming 5 attainments:
2. Hypothecation reckon
4. Manliness time
5. TDR Status
Follow the adown tramps to adequate your contrivance:
1. Preparation: (1 summit) Constitute 3 new tabs/worksheets in the Excel improve. Designate them “Recon”, “Difference”, and “CAP”.
2. Reorder your tabs in a way that supervene this order: SQ1 > Recon > Discord > CAP > OverRides > Notes
3. Use your capacityal prudence to secure formatting all cells resisting productionsheets is consonant and expend. For issue, all cells subordinate a specific post should be Accounting formatted delay 2 decimals. Retrospect cells and apsubject-matter any monstrous formatting conformably.
4. CAP: (1 summit) Introduce the CAP facts using a unfailing Excel capacity from the Melody improve to the CAP tab on the Excel improve.
5. Capture the screenshots of all of your tramps to introduce the Melody improve to Excel. Save those screenshots at the determined area on Notes productionsheet.
6. Recon: (8 summits)
a. Image “SQ1”in cell A1 and link the cell for A1 through G1 and highlight it in Inexperienced color
b. Discover the overhead mentioned 5 key attainments as well-mannered-mannered-behaved-behaved as Loan_Purpose, Collateral Description onto row 2. Your foremost few posts and rows should behold relish the forthcoming.
0. Apply the ooze to row 2, so you can execute ooze at any time
0. Leave post H blank
0. Designate the cell I2 as “Match”.
0. Add the 5 key attainments for CAP subordinate post J through N. You may not add/remove post/row.
0. Repeat tramp 6.a and image “CAP” instead of SQ1”. Highlight the cell delay sky bluish-colored.
0. Leave the post O blank
0. Subordinate post I, transcribe a formula subordinate post I that it verifies if the hypothecation reckon subordinate SQ1 sort (post B) is resembling to appraises subordinate CAP sort of Recon tab (post K). In other articulation, populate a formula subordinate post K to discover pairing hypothecation # in CAP. Then, transcribe a formula subordinate post I to parallel posts B & K.
If a hypothecation reckon is a pair, relishness “0”, inadequately relishness “1”. You may not add/remove post/row. Move to later tramps
0. Enter the 5 key attainments designates on cell P2 through T2. You may not add/remove post/row.
0. Add 2 posts in U and V and designate them Completion and Overrides relatively
0. Repeat tramp 6.a and image “Diff” instead of “SQ1”. Highlight the cell delay red.
m. Use referencing (='SQ1'!B2), instead of hardcoding approvenesss to populate appraises in posts A and B for SQ1 facts. Use a formula (save referencing) to draw the expend appraise for posts C through G. You may not add/remove post/row.
7. Use diverse formula(s), save referencing, to behold up relative appraises for CAP posts J through N. You should not use referencing for CAP approvenesss. Instead, use formula(s) such as, If, Iferror, and vlookup to beholdup appraises in the CAP productionsheet. Preferably, use formulas that we discussed in the tabulate. You may add posts/rows. Capture the screenshots of all of your tramps for post L (Principal). Save those screenshots at the determined area on Notes productionsheet.You can use referencing for post J. Utilize unfailing formula(s) subordinate post P through T as you see fit to adequate the concordantity plan among 5 key attainments among SQ1 sort (green) and CAP sort (blue). In the episode any of these attainments’ appraises were the identical in SQ1 & CAP, relishness 0, inadequately relishness 1. Specifically, use nested formula for post T.
7. Transcribe formula(s) subordinate post U that adds the reckon of discrepancies adapted from posts P to T.
7. Transcribe formula(s) subordinate post V (Overrides) to behold up Codes and Comments cognate to each hypothecation in Overrides productionsheet. If useful, your formula in post V should relishness comments in the Overrides productionsheet post E (Codes) utterly delay a burl “-” in-among and the superveneed by post I (Comments). The concert of post E (Codes) and I (Comments) should be reflected in one cell. For issue, for hypothecation #SQ1-000050144 your override formula must relishness: “Payoff Time - Fully pregnant off hypothecation”
7. Utilize freeze panes capacity in Excel to secure the cells that include headers (row2), hypothecation reckons, and times (post A and B) are regularly relishnessing/fixed, inunanalogous of scrolling up, down, fit or left in the productionbook.
7. Difference: (5 summits)
a. Image “Loans not in CAP” in cell A1, and “Loans delay attainment variances” in cell A20. Designate D2 as “SQ1” and E2 as “CAP”
b. Introduce formula(s) in D3 and E3 and adown to enacts all the SQ1 hypothecation reckons that do not exist in CAP.
c. Designate cell D21 as “Loan #”, and E21 as “Attribute Designate ” Do a concordant plan as developed tramp for hypothecations delay unanalogous key attainments in Cell D21 and G21. You can use referencing for cells subordinate post D. You may populate facts subordinate post E delay formula. Your production should behold relish the forthcomings. The reckons or attainments appeared on screenshot are provided and merely for grant purposes. If a hypothecation is not in CAP (detriment in CAP), then it should not relishness up subordinate the “Loans delay attainment variances” consideration.
d. Generate a pivot consideration in Discord productionsheet cell G3 that relishnesss the Hypothecation Purpose (SQ1 productionsheet post AE) delay the percentage of each sort paralleld to the important completion. Categories delay 0.00% must be not relishnessing/eliminated.
e. Populate a inventory at B28 to secure you feel not missed any hypothecations in your tabulateification transmutation pacification. Execute a adequateness curb to secure all and merely hypothecations in SQ1 made it to CAP. To do that, image “SQ1” and “CAP” in D28 and D29. Image a formula in E28 and E29 to sum the reckon of hypothecations in each factsset. Get the SQ1 hypothecation bulk from the SQ1 tab. Similarly, get the CAP hypothecation sum from the CAP tab. Calculate the discord in E30. Discord is adapted by SQ1 approveness minus CAP. Analyze the product in one condition delay hither than 100 articulation in Cell A32. Express your notion environing whether the facts transmutation was lucky or not. Support your notion delay discoverings in the Excel improve. Your developed production should behold relish the forthcoming. The reckons or attainments appeared on screenshot are provided and merely for grant purposes.
f. Generate a 3D Pie Bar for the consideration referenced in the tramp overhead (e) on cell G20 that relishnesss and the relative pie hunk for SQ1 & CAP. Each pie must relishness sort designate, appraise, and percentage. Those attainments must be placed at the without end of the pie hunks. Secure the pie style reads “Quantity Curb Figure”. Highlight the SQ1 hunk in inexperienced, and CAP in sky bluish-colored.
g. Enter your designate and today’s time into cell D35 & D36.
h. Retrospect your answers and formula for adequateness, atonement, and expendness.
i. Submit your Excel improve on Canvas
1. This is a knot contrivance. Members of a team get accept the identical charges.
2. There shall not be any image of manifest errors in the complete productionsheets. Errors could be relishnessing up as #N/A, #REF, #Error, etc.
3. All the formula at the foremost profitable row should be the identical as the forthcoming rows.
4. The Excel Contrivance is due as precise on the syllabus. Late inferiority or emailed contrivances get not be legitimate.
5. Submit your Excel improve merely. Designate your Excel improve as the forthcoming:
The tabulate reckon is a 5 digit reckon, which is inventoryed on your syllabus as well-mannered-mannered-behaved-behaved as Canvas.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more