Attached is a balance sheet I am to complete. The numbers in red were given to us by the Professor. I am getting hung up on determining the cost of the bank note (line 24). Then on line 30, the numbers do not seem to be correct for the last 2 columns. Line 53 is a very basic question. On the excel sheet, the questions are next to the data.

Budget Project Due Date - Thursday - January 22, 2016 Assignment: How much will be owed to the bank on June 30, 2016? Prepare a monthly income statement and monthly balance sheet for the company FINA 756 for the first six month of 2016. Also reconcile between monthly earnings and the amount of cash available to reduce the bank note. Neat Presentation is important. (page breaks and formatting will be evaluated.) Information obtained during interview: Sales in November and December 2015 were $100,000 and 110,000, respectively. Gross profit averages 46% General and administrative expense approximate 31% of sales. Sales in 2016 are expected to increase 5% a month. Inventory at month end is targeted to equal the following two months anticipated sales Cash at December 31, 2015 was $25,000 and is expected (budgeted) to increase by $1,200 per month. Other assets at 2015 year end was $16,000 and expected to remain at the same level. Common stock is valued at $50,000 and no additional sales expected. Accounts payable at each month end equals 75% of month end inventory. Accounts receivable turn over approximately 12 times. Bank Note was $120,000 at December 31, 2015. For this exercise you can assume no income tax expense and no interest expense. All extra cash not needed to maintain the budgeted cash balance is used to reduce the bank note. FINA 756 Budget Forecast December January February March April May June Income Statements: Sales Cost of goods sold Gross profit General & adm. expenses Net Income 110,000 59,400 50,600 34,100 16,500 115,500 62,370 53,130 35,805 17,325 121,275 65,489 55,787 37,595 18,191 127,339 68,763 58,576 39,475 19,101 133,706 72,201 61,505 41,449 20,056 140,391 75,811 64,580 43,521 21,059 147,411 79,602 67,809 45,697 22,112 Balance sheets: Assets: Cash and cash equivalents Receivables Inventories Total current assets Other non-current assets Total assets 25,000 110,000 236,775 371,775 16,000 387,775 26,200 115,500 248,614 390,314 16,000 406,314 27,400 121,275 261,044 409,719 16,000 425,719 28,600 127,339 274,097 430,035 16,000 446,035 29,800 133,706 287,801 451,307 16,000 467,307 31,000 140,391 302,192 473,583 16,000 489,583 32,200 147,411 317,301 496,912 16,000 512,912 Liabilities: Accounts payable Bank note Total liabilities 177,581 120,000 297,581 186,460 111,891 298,351 195,783 104,226 300,009 205,572 215,851 226,644 237,976 **what are you using to decrease note the value on li Equity: Common stock Retained earnings Total equity Total liabilities and equity 50,000 40,194 90,194 387,775 50,000 57,962 107,962 50,000 75,710 125,710 50,000 94,810 144,810 50,000 50,000 135,925 XXXXXXXXXXXXX 50,000 158,037 208,037 ** here is where my numbers start getting weird. Sho 17,325 8,879 26,204 18,191 9,323 27,514 19,101 9,789 28,890 20,056 10,279 30,334 21,059 10,793 31,851 22,112 11,332 33,444 1,200 5,500 11,839 1,200 5,775 12,431 1,200 6,064 13,052 1,200 6,367 13,705 1,200 6,685 14,390 1,200 7,020 15,110 8,109 7,665 111,891 104,226 154,781 162,520 added extra 2 mo 301,225 Reconciliation between Net income and cash reduction in Notes payable: Source of funds: Net Income Increase in accounts payable Use of funds Cash Accounts receivables Inventory 16,500 Cash available to pay bank note Prior month bank note balance Expected bank note balance Ending bank note balance 120,000 ** this number is directly tied to line 24. 95,652 86,589 ** is ending balance lines 51 minus 42? onths for inventory calc line 24? ouldn't the X be =G28+G29? Fina 470 Budget Forecast December Income Statements: Sales Cost of goods sold Gross profit General & adm. expenses Net Income Balance sheets: Assets: Cash and cash equivalents Receivables Inventories Total current assets Other non-current assets Total assets February March April May June 110,000 62,370 55,787 39,475 20,056 25,000 115,500 261,044 430,035 16,000 489,583 Liabilities: Accounts payable Bank note Total liabilities Equity: Common stock Retained earnings Total equity Total liabilities and equity January 186,460 104,226 301,225 50,000 135,925 208,037 387,775 Reconciliation between Net income and cash reduction in Notes payable: Source of funds: Net Income Increase in accounts payable 17,325 9,323 28,890 Use of funds Cash Accounts receivables Inventory 1,200 6,685 15,110 Cash available to pay bank note Prior month bank note balance Expected bank note balance Ending bank note balance 8,109 120,000 95,652 86,589