Question

1 Approved Answer

Posted on Jun 17, 2024

12.6 Excel Problem Task: Create a depreciation schedule to monitor your company?s investment in fixed assets. a. Your spreadsheet should have the following column headings:

12.6 Excel Problem Task: Create a depreciation schedule to monitor your company?s investment in fixed assets. a. Your spreadsheet should have the following column headings: Account number (5 digits, valid range from 11000 to 19999) Description (text, valid entries = software, laptop, monitor, etc. Date placed in service (mm/dd/yyyy format) Estimated useful life Acquisition cost Beginning accumulated depreciation (prior to this year) Current period depreciation (SL method, assume no salvage value) Ending accumulated depreciation (beginning accumulated depreciation plus current period depreciation Net book value (acquisition cost-ending accumulated depreciation) b. In the top rows of the spreadsheet enter the following data Your name in row 1 Depreciation schedule for year (in row 2) Two cells to the right of the text ?Depreciation schedule for year?, a formula that returns the current year (ie, if the date is 10/31/2009 your formula should return the value 2009) c. Enter the following data Already done A4:E16 d. Create formulas to calculate the beginning balance of accumulated depreciation, current depreciation, ending balance, and net book value. Use the built-in Year (xxx) function to return the year portion of a date in the mm/dd/yyyy format The today () takes no arguments and returns the current date in mm/dd/yyyy format Assume that assets placed in service anytime during a year can be depreciated for the entire year. e. Include the following validation checks, with appropriate and informative error messages should any of these be violated: N/A Each entry in the columns account number, description, and date contains allowable values. If an asset has already been fully depreciated, current depreciation should be zero Ending balance of accumulated depreciation should equal beginning balance plus current depreciation Net book value should equal acquisition cost minus ending balance of accumulated depreciation The sum of all net book values should equal the sum of all acquisition costs minus the sum of all ending balances of accumulated depreciation. f. Create a table at the bottom of the worksheet that consists of two columns: Asset name (values should be chair, desk, laptop, monitor, software, and workstation) New book Value (use as a built-in Excel function to calculate this number) I have done a,b,& c... image text in transcribed

ACC 451 Project 2 Excel Template Students MUST use this template and submit this only file in the Drop Box by the end of week 5 Assigned problems P10-4 P10-14 P11-6 P12-6 Use each tab below (if you cannot see any tabs, click and grab the worksheet box-drag to a smaller vertical size and you will see the tabs), to answer questions on each assigned problem. Do not delete any tabs When you are finished, save your excel file as follows: LastNamePROJECT2ACC451.xlsx 12 Month Cash Budget Month 1 Beginning Cash Balance Month 2 $350,000 Month 3 $2,350,000 $5,870,000 Month 4 Month 5 $10,425,200 $15,425,952 Month 6 $20,476,712 Month 7 $25,577,979 Month 8 $30,730,258 Month 9 $35,934,061 Month 10 Month 11 Month 12 $41,189,902 $46,498,301 $51,859,784 Total Sales $5,000,000 $5,050,000 $5,100,500 $5,151,505 $5,203,020 $5,255,050 $5,307,601 $5,360,677 $5,414,284 $5,468,426 $5,523,111 $5,578,342 Cash From Sales-Current $2,000,000 $2,020,000 $2,040,200 $2,060,602 $2,081,208 $2,102,020 $2,123,040 $2,144,271 $2,165,713 $2,187,371 $2,209,244 $2,231,337 $1,500,000 $1,515,000 $1,530,150 $1,545,452 $1,560,906 $1,576,515 $1,592,280 $1,608,203 $1,624,285 $1,640,528 $1,656,933 $1,000,000 $1,010,000 $1,020,100 $1,030,301 $1,040,604 $1,051,010 $1,061,520 $1,072,135 $1,082,857 $1,093,685 $400,000 $404,000 $408,040 $412,120 $416,242 $420,404 $424,608 $428,854 $433,143 $15,425,952 $20,476,712 $25,577,979 $30,730,258 $35,934,061 $41,189,902 $46,498,301 $51,859,784 $57,274,881 A/R Collection-Last Month $- A/R Collection-Two Months $- $- A/R Collection-Three Months $- $- A/R Bad Accounts - Do we need this? $- $- Ending Cash Balance Checkfigures $2,350,000 $2,350,000 $5,870,000 $5,870,000 $10,425,200 Note: please read the instructor's posting about form controls in the QA discussion area. Assumptions Initial Sales Growth Rate % of Sales Cash % of Sales Collected Next Month % of Sales Collected in Two Months % of Sales Collected in Three Months % of Sales Never Collected Initial Cash $5,000,000 1.0% 40% 30% 20% 8% 2% $350,000 Use arrows to change Initial Sales. Assign a format control to cell D22 which will allow a change to B15 when the slider moves This is under the developer tab; hit INSERT, then select form controls. Use arrows to change Growth Rate. Assign a format control to cell D21 which will allow a change to B14 when the slider moves Note there are formulas in B14 and in B15 based on the spinner and scroll items 5000 See page 77 of journal article for explanation 10 See page 76 of journal article for explanation Spinner and Scroll Help: On page 76, the journal article states, "Start by typing 100 in cell D22... For this worksheet, follow these instructions: Start by typeing 100 in cell D22. Then inset the formula =d22/1000 in cell B15. In your spinner control box you will use 10, 5, 15, 1, 1 and D22 On page 78, the journal article states, "In cell D21, place the value 1000 and change B17... For this worksheet, follow these instructions: In cell D21, place the value 5000 and change cell B14 to this formula =1000*D21 You will have to adjust your scroll control box in a similar fashion to what I did for the spinner Product Widgets Widgets Widgets Gadgets Gadgets Gadgets Gizmos Gizmos Gizmos Wingdings Wingdings Wingdings Dohingeys Dohingeys Dohingeys Thingamabobs Thingamabobs Thingamabobs Whatchamacallits Whatchamacallits Whatchamacallits Round2its Sales($) Division 245000 A 74000 B 178944 C 123678 A 163888 B 201999 C 178000 A 123456 B 198346 C 158255 A 273050 B 95000 C 333000 A 144357 B 188500 C 184750 A 222000 B 100000 C 278000 A 200000 B 129905 C 103000 A Number of Products with Sales > 200,000 Insert function into cell b25 as required in instructions Hint:Use Sumif functions for each of these items. Total Sales for All Products of Div A Insert function into cell b27 as required in instructions Division to Sum Total Sales For All Products in That Div. A Allow for an entry of A, B, or C by user Insert function into cell b31 as required in instructions Insert conditional formatting as shown in item e. When your instructor selects cells B1..b23, he/she will be able to see your formatting rules in the appropriate place. Did you do this? Y or N Answer the Y/N? Y 12 Month Cash Budget Month 1 Beginning Cash Balance Month 2 Month 3 $350,000 #VALUE! Total Sales $5,000,000 $5,050,000 Cash From Sales-Current $2,000,000 $2,020,000 A/R Collection-Last Month $- A/R Collection-Two Months $- $- A/R Collection-Three Months $- $- New Cash added Expected Cash Outflows Cash (needed) or available Cash Borrowed if needed $2,000,000 $$2,350,000 $- $1,500,000 $3,520,000 $#VALUE! #VALUE! Cash Paid on Note if possible #VALUE! #VALUE! Cumulative Borrowings-Note Payable #VALUE! #VALUE! #VALUE! $1,550,000 #VALUE! $1,918,500 Total Ending Cash Balance After Deducts Check Figures -Cumulative Borrowings Assumptions Cash Outflow Percentage Minimum Cash Allowed Please list the data validation controls you used and explain where your instructor can find these? Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12 Depreciation Schedule for Year Account Number 11001 11100 11200 11050 11500 11500 11500 11300 11300 11300 11001 11001 11050 Description Desk Laptop Workstation Chair Software Software Software Monitor Monitor Monitor Desk Desk Chair Date Placed Estimated In Service Useful Life 04/05/2006 05/02/2007 03/25/2006 02/01/2003 07/01/2008 06/30/2007 01/31/2005 02/20/2007 09/30/2003 10/15/2006 04/04/2007 08/08/2003 06/30/2008 5 5 5 5 4 4 4 3 3 3 5 5 5 2009 Acquisition Cost Beginning Accumulated Depreciation $500 $2,400 $1,900 $750 $750 $2,100 $900 $800 $1,200 $600 $1,000 $1,500 $1,250 The year MUST be 2009 even if we are in a future year. Use SL depreciation without partial amounts. Ending Accumulated Depreciation Net Book Value Insert functions to finish out the table using Excel to solve for all depreciation items. You cannot have a negative net book value Description Software Laptop Monitor Workstation Desk Chair $480 $250 sum Asset Names Software Laptop Monitor Workstation Desk Chair Current Period Depreciation $480 $0 New Book Value Insert functions in cells B19-b24 to complete this chart. You must use functions which would change if the year changes Note: Item b - 3rd bullet point - please ignore as specified above. Item e in the problem. All items must be done with the exception of the first bullet point under e. ACC 450 Project 2 Grading Totals Problem Prob 10-4 Part a Part b Part c Part d Prob 10-14 Part a Part b Part c Part d Part e Prob 11-6 Part a Part b Part c Part d Part e Part f Prob 12-6 Part a Part b Part c Part d Part e Part f You instructor will fill out this grading rubric. Please do not remove. 100 Max 100 Earned Notes: 10 5 5 5 10 5 5 5 5 5 5 5 5 5 5 5 5 5 5 4 4 4 4 4 5 4 4 4 4 4 5 4 4 4 4 4 5 4 4 4 4 4