ACC EXCEL
MaxZhou
SUB3.xlsxBalance sheet Screen Shot
Income Statement Screen Shot
Stock Price to Sales Budget
| Step 1 | Step 2 | Step 3 | Step 4 | Step 5 | ||||||
| "=abs(number)" | 4th Quarter Budgeted Sales | Look at the Sales (Revenue) total from your company's income statement. See image below. | ||||||||
| Date | Day of Week | Closing Price | Opening Price | Change in Price | Multiplier | Absolute Value | October | November | December | |
| 12-Oct | Monday | $ 82.38 | $ 77.92 | 4.46 | $ 446,000.00 | 446,000 | October Sales | $ 1,207,000 | $ 1,218,000 | $ 1,274,000 |
| 13-Oct | Tuesday | $ 78.60 | $ 82.32 | -3.72 | $ (372,000.00) | 372,000 | ||||
| 14-Oct | Wednesday | $ 79.67 | $ 79.29 | 0.38 | $ 38,000.00 | 38,000 | ||||
| 15-Oct | Thursday | $ 76.19 | $ 77.74 | -1.55 | $ (155,000.00) | 155,000 | ||||
| 16-Oct | Friday | $ 75.34 | $ 73.38 | 1.96 | $ 196,000.00 | 196,000 | ||||
| 19-Oct | Monday | $ 70.83 | $ 70.58 | 0.25 | $ 25,000.00 | 25,000 | November Sales | |||
| 20-Oct | Tuesday | $ 74.87 | $ 72.62 | 2.25 | $ 225,000.00 | 225,000 | ||||
| 21-Oct | Wednesday | $ 68.16 | $ 72.72 | -4.56 | $ (456,000.00) | 456,000 | ||||
| 22-Oct | Thursday | $ 62.10 | $ 62.95 | -0.85 | $ (85,000.00) | 85,000 | In this example, the annual total is 18,611. On average, this is revenue of $1,550.92 per month | |||
| 23-Oct | Friday | $ 69.92 | $ 65.65 | 4.27 | $ 427,000.00 | 427,000 | You have to make your sales forecast make sense for your most recent income statement | |||
| 26-Oct | Monday | $ 58.59 | $ 60.32 | -1.73 | $ (173,000.00) | 173,000 | December Sales | Therefore, I will move the decimal three places in my forecast which will give me the following: | ||
| 27-Oct | Tuesday | $ 58.98 | $ 59.55 | -0.57 | $ (57,000.00) | 57,000 | ||||
| 28-Oct | Wednesday | $ 56.33 | $ 55.38 | 0.95 | $ 95,000.00 | 95,000 | ||||
| 29-Oct | Thursday | $ 61.41 | $ 56.23 | 5.18 | $ 518,000.00 | 518,000 | 4th Quarter Budgeted Sales | |||
| 30-Oct | Friday | $ 58.03 | $ 62.34 | -4.31 | $ (431,000.00) | 431,000 | October | November | December | |
| $ 120,700 | $ 121,800 | $ 127,400 | ||||||||
| This is the budgeted sales forecast I will use to make my Master Budget |
Balance Sheet
| Statement of Financial Position as at 31 December 2019 | |
| Particulars | Amount (CNY Million) |
| Assets | |
| Goodwill and intangible assets | 8822 |
| Property, Plant and Equipment | 97719 |
| Right of use of Assets | 17417 |
| Long-Term Leasehold Repayments | 0 |
| Interests in associates and Joint Ventures | 731 |
| Other Investments, Including Derivatives | 7266 |
| Deffered Tax assets | 10877 |
| Contract assets | 2200 |
| Trade and bill Recievable | 4540 |
| Other Assets | 5196 |
| Non-Current Assets | 154768 |
| Inventories and other contract costs | 167390 |
| Contract assets | 50812 |
| Trade and Bill Recievable | 85525 |
| Other Assets | 29126 |
| Other Investments, Including Derivatives | 200356 |
| Cash and Cash Equivalents | 170684 |
| Current Assets | 703893 |
| Total Assets | 858661 |
| Equity | |
| Equity attributable to equity holders of the company | 295106 |
| Non-Controlling Assets | 431 |
| Total Equity | 295537 |
| Liabilities | |
| Loans and Borrowings | 104531 |
| Deffered Government Grant | 1013 |
| Deffered Tax Liability | 1755 |
| Lease Liabilities | 6413 |
| Other Liabilities | 3157 |
| Non-Current Liabilities | 116869 |
| Loans and Borrowings | 7631 |
| Employee Benefits | 98375 |
| Income Tax Payable | 3909 |
| Trade and Bills Payable | 142185 |
| Contract Liabilities | 69327 |
| Lease Liabilities | 3274 |
| Other Liabilities | 106005 |
| Provisions | 15549 |
| Current Liabilities | 446255 |
| Total Liabilities | 563124 |
| Total Equity and Liabilities | 858661 |
Income Statement
| Statement of Comprehensive Income for the year ended 31 December 2016 | October | November | December |
| 120700 | 121800 | 127400 | |
| Particulars | Amount (CNY Million) | Monthly Average | |
| Revenue | 858833 | 71569 | Other information from the statements necessary to make the budget |
| Cost of Sales | -536144 | 1) Cost of Sales are 62% of Sales | 62% |
| Gross Profit | 322689 | 2) Variable Expenses/Revenues = | 29% |
| Research and Development Expenses | -131659 | 3) Fixed Expenses are 13.29% | 13.29% |
| Selling and administrative Expenses | -114165 | 4) Income tax rate is 19.69%% (Income Tax Expense/Earnings before Taxes) | 19.70% |
| Other Income Net | 970 | 6) Interest Rate is ~3% per year (Interest Expense/Long Term Debt) | 3% |
| Operating Profit | 77835 | ||
| Finance Income and Expenses | 178 | ||
| Share of associates and Joint Ventures results | 10 | ||
| Profit Before Taxation | 78023 | ||
| Income tax | -15367 | ||
| Profit After Tax | 62656 | ||
| Other Comprehensive Income | |||
| Comprehesive Income- Net Change in Fair Value | 186 | ||
| Items that will not Classified to profit or loss | 148 | ||
| Items that are may be classified to Profit or Loss | 334 | ||
| Non-Equity Financial at FVOCI- Net change in Faire value and impairment | -14 | ||
| Translation Differences on Foreign Operations | 1881 | ||
| 1867 | |||
| Total Other Comprehensive Income | 2201 | ||
| Total Comprehensive Income | 64857 | ||
| Profit for the year attributable to | |||
| Equity Holders of the company | 62605 | ||
| Non-Controlling Interest | 51 | ||
| Total Comprehensive Income attribuatable to | |||
| Equity Holders of the company | 64806 | ||
| Non-Controlling Interest | 51 | ||
Master Budget
| Master Budget for October November December | ||||
| a. | Sales Data | |||
| August | September | October | November | December |
| 71569 | 71569 | 120700 | 121800 | 127400 |
| b. | Variable Expenses | |||
| August | September | October | November | December |
| 20566 | 20566 | 34684 | 35000 | 36610 |
| c. | Fixed Expenses | |||
| August | September | October | November | December |
| 15176 | 15176 | 15176 | 15176 | 15176 |
| d. | Cost of Sales | |||
| August | September | October | November | December |
| 44679 | 44679 | 75349 | 76036 | 79532 |
