I am having a hard time with this challenge. My numbers are not accurate when I go to check my numbers. Could someone walk me through this template.
Safari . bill 1:47 PM C6-Student-Template AD Q . . . fx V O B C D E G TASK #4 FINANCIAL STATEMENTS, YEAR 5 High-End Lifts, Inc. Income Statement December 31. Year 5 $ % Sales Revenue #DIV/O! C6 = Task #1 OB G9 Cost of Goods Sold #DIV/O! C7 = Task #1 OB G15 Gross Margin #DIV/O! SG&A Expense #DIV/O! C9 = Task #1 OB G63 Operating Income #DIV/O! Interest Expense #DIV/O! C11 = Task #1 OB G69 Earnings Before Taxes #DIV/O! Ck: C12 = $15,978,919 Income Tax Expense, 25% #DIV/O! C13 = 25% of EBT Net Income #DIV/O! Ck: D14 = 23.8% High-End Lifts, Inc. Balance Sheet December 31. Year 5 Assets $ % Current Assets Cash 2.9% C21 = Task #3 FB C48 A/R 41.5% C22 = -Task #3 FB C6 (make sure answer is positive) Supplies 3.0% C23 = INFO BS Year 4 C9 - Task #3 FB C24 Raw Materials Inventory 2.9% C24 = Task #1 OB G30 Work-in-Process Inventory 3.2% C25 = INFO BS Year 4 C11 (no change in account) Finished Goods Inventory 18.0% C26 = done for you (study formula) Prepaid Insurance 0.9% C27 = INFO BS Year 4 C13 - Task #3 FB C25 Total Current Assets 72.4% 1,413,804 PP&E (SG&A Computers & Network (5 yr. life) 0.9% C30 = INFO BS Year 4 C16 + Task #2 CE C5 Furniture & Fixtures (10 yr. life) 1.0% C31 = INFO BS Year 4 C17 + Task #2 CE C6 Office Equipment (5 yr. life) 0.5% C32 = INFO BS Year 4 C18 + Task #2 CE C7 Vehicles (5 yr. 2.9% C33 = INFO BS Year 4 C19 + Task #2 CE C8 PP&E (Production) Land 1.4% C35 = INFO BS Year 4 C21 + Task #2 CE C10 Buildings 14.9% C36 = INFO BS Year 4 C22 + Task #2 CE C11 Furniture & Fixtures (10 yr. life) 0.2% CAD Computers, Network (5 yr. life) 0.9% C37 = INFO BS Year 4 C23 + Task #2 CE C12 Machinery (15 yr. life) 6.8% C38 = INFO BS Year 4 C24 + Task #2 CE C13 Forklift (10 yr. life) 1.7% C39 = INFO BS Year 4 C25 + Task #2 CE C14 A/D Depreciation 3.5% C40 = INFO BS Year 4 C26 + Task #2 CE C15 Total PP&E 27.6% C41 = INFO PP&E F18 (make sure it's negative) Total Assets 100.0% C43 Ck = $14,148,008 Liabilities Current Liabilities Accounts Payable 13.5% C46 = Task #4 FB C13 Wages Payable 1.2% C47 = INFO BS Year 4 C33 + Task #4 FB C26 Interest Payable 7,975 0.0% C48 = GIVEN; study formula Income Taxes Payable 1.9% C49 = INFO BS Year 4 C35 + Task #4 FB C28 Unearned Revenue 1.3% C50 = INFO BS Year 4 C36 + Task #4 FB C29 Current Portion of LT Notes Payable 0.2% C51 = INFO BS Year 4 C37 + Task #4 FB C30 Total Current Liabilities 7,975 18.2% C52 = Ck = $1,715,05 Notes Payable 936,113 0.6% C54 = Ck = $2,651,169 Total Liabilities 944,088 18.8% Stockholders' Equity Common Stock 4.9% C56 = INFO BS Year 4 C42 Retained Earnings 76.3% C57 = INFO BS Year 4 C43+ Task #5 FS C14 + Task #4 FB C45 Total Equity 81.2% Total Liabilities & Stockholders' Equity 944.088 100.0% C59 Ck = $14,148,008 60 (944,088) 62 63 65 66 67 68 69 70 71 73 75 76 77 Task #3 FB Task #4 FS INFO BS Yea +Safari . 1:46 PM C6-Student-Template AD Q . . . fx Sales Mix (from Challenge 4) V B D F G H K TASK #1 OPERATING BUDGET, YEAR 5 Stair-Lifts Elevators Economy Premium Lite Comm Total SALES BUDGET, YEAR 5 (from Challenge 4) 72% 7% 2% 19% 100% Sales Volume (from C5, 12 mo goal; total is WA Units) 132 2,238 307 2,238 683.89 Sales Price 275,000 $ 1,575 $ 4,275 Sales Revenue, in $ C9 = C7 x C8 (copy across to columns D through F). G9 = sum (C9:F9) COST OF GOODS SOLD, YEAR 5 ost of Goods Sold $ 840,000 G13 = C13 14 s: $70,000 FC/month + VC (Sales$ x .45 s: $21,500 FC/month + VC (Sales$ x .45) $ 59,000 $ 25,000 $ 174,000 G14 = sum (D14:F14) 15 of Goods Sold C15 = C13+C14 (copy across to columns D through F). G15 = sum (C15:F15) (CK: $23,755,586) 16 Unit Cost By Product-Line C16 = C15/C7 (copy across to columns D through F) 17 PRODUCTION BUDGET, YEAR 5 Sales Volume C19 = C7 (copy across to columns D through F) 20 d Units in Ending FG Inventory Row 20 is given (formula) Needed 21 = C19+C20 (copy across to columns D through F) 22 n Beginning FG Inventory (2) (58) (6) (50) Row 22 is given 23 To Be Produced C23 = C21 + C22 (copy across to columns D through F) C24 = C16 (copy across to columns D through F) 24 Budgeted Cost 25 Cost of Goods Manufactured C25 = C23 x C24 (copy across to columns D through F) G25 = sum C25:F25) Ck: $24,644,976 26 DIRECT MATERIALS BUDGET, YEAR 5 28 Cost of Goods Manufactured C28 = C25 (copy across to columns D through F); G28 = sum (C2 29 Cost Related to DM = 40% E and 51/55/70% SL C29 = C28 x 0.40; percentages are given in B29; CK: G29 = $11, 30 d Ending Raw Materials Inventory C30 = (C29/365) x 20 days (copy across to columns D through F 31 Material Needed C31 = C29 + C30 (copy across to columns D through F); G is total 32 Materials in Beginning Balance Inventory C32 done for you (BB from balance sheet times sales mix); dor 33 (184,824) $ (17,969) $ (5.134) 5 (48,773) 5 (256,700) C33 = C31 + C32 (net); copy across to columns D through F; G is 34 Purchases of Raw Materials Ck: G33 = $11,879,458 DIRECT LABOR BUDGET, YEAR 5 ost of Goods Manufactured C36 = C25 (copy across to columns D through F); G is total Cost Related to DL = 50% E and 38/22/21% SL C37 = C36 x 0.50; percentages are given in B37; CK G37 = $10,6 38 Per Unit C38 = C37/C23 (copy across to columns D through F) 39 Per Hour: 40 (400 hours each) C40 = C38/400 hours; copy format for D41, E42, F43; CK F43 = y Stair Lifts (10 hours each) Stair Lifts (12 hours each) mercial (15 hours each) MANUFACTURING OVERHEAD BUDGET, YEAR 5 ost of Goods Manufactured Cost Related to MOH = 10% E and 11/23/9% SL C46 = C25 (copy across to columns D through F); G is total C47 = C46x0.10; percentages are given in B47; Ck: G47 = $2,495 COST OF GOODS MANUFACTURED BUDGET, YEAR 5 erials Used C50 = C29 (copy across to columns D through F); G is total 52 c51 = C37 (copy across to columns D through F); G is total ring Overhead C52 = C47 (copy across to columns D through F); G is total 53 f Goods Manufactured C53 = sum(C50:C52) copy across to columns D through F; G is to 54 Ck: G53 = $24,644,976 SELLING, GENERAL & ADMINISTRATIVE EXPENSES, YEAR Expense (10,000 per month + $7362x) G56 =(10000 x 12)+(7362 x G7) missions ($3681x) Use information given in column B for each row. 58 59 Wages Expense ($128,232 per month + 801x on Expense ($8,240 per month) G63 = sum (G56:G62) 60 xpense ($5,900 per month + 544x) 61 se ($12,000 per month) Ck: G63 = $10,519,097 62 pense ($2,000 per month + 74x) 63 A Expenses 64 8,522,633 65 66 S (8,522,633) 67 68 Interest & Income Tax Expense 69 pense 70 $ 96,589 | G69 = done for you (sum of interest incurred during year) Expense 71 3,994,730 G70 = done for you Income tax expense based on earnings bef G71 = G69+G70 72 73 74 75 76 77 78 79 80 81 ame and File Challenge Task #1 OB +4 Safari . CAPITAL EXPENDITURES BUDGET, YEAR 5 5681A All values are given. Computers 8: Network (5 yr. life] C5 = 15,000 Furniture EL Fixtures (10 yr. life] C6 = 18,000 C7 = 8,400 Office Equipment (5 yr. life) C8 50 000 Vehicles (5 yr. life) PP&E (Production) Land C10 = 25,000 Buildings C11 = 260,000 Furniture & Fixtures (10 yr. life) C12 = 3,600 CAD Computers, Network (5 yr. life) C13 = 15'000 Machinery (15 yr. life] E1: : $86330 Forklift (10 yr. life) Ck: C16 = 543 040 Total Task #2 CE Safari . 1:47 PM C6-Student-Template AD Q . . . fx C D E G H TASK #3 FINANCIAL BUDGET, YEAR 5 DIRECTIONS: Complete the spreadsheet and then go to Bb to JOUTOWN Cash Receipts Budget complete the assignment in multiple choice format. ear 5 ales Revenue C4 = Task #1 OB G9 dd: Year 4 A/R Balance Collected in Year 5 C5 = INFO BS Year 4 C8 ess: Year 5 Credit Sales Not Collected Yet, assume 45 days A/R Balance C6 = (-C4/365) x 45 total Cash Receipts from Sales 9 Cash Disbursements Budget 10 ear 5
