Required:Prepare a comprehensive 6-month budget, including supporting schedules and a report for the period January 1, 2020 to June 30, 2020 for Imagine, Inc (a fictional company).This project must include:
- Sales Forecast and Budget
- Cash Receipts budget
- Purchase budget
- Cash Purchases Disbursements budget
- Operating Expense budget
- Summary Cash budget
- Budgeted Income Statement
- Budgeted Balance Sheet
- A written Summary Report
Notes and Hints
1.All 9 parts must be submitted before I grade the project.
2.The schedules/budgets must be prepared on Excel. The templates I have prepared must be used as is.
3.Part of this project is demonstrating proper use of Excel. You may only input a "hard number" into a pink cell. All yellow cells must be formula based (no numbers included - use appropriate cell referencing).
4.I recommend constructing the formulas for one month and then copying the formulas over to the remaining months.
5.Rounding is encouraged and you may ignore interest and taxes.
- The budget templates and this instruction sheet are located on the budget project module. Make sure you save the file to excel and then open the file through Excel (not Internet Explorer).
- Check figures are also located on the budget project module.
- A written summary report, outlining the main issues and problems identified during the budgeting process and suggestions for improving the budget forecast. Don't explain how you prepared the budget. Analyze the issues, including profitability, cash needs, and cost structure. Suggest at least 3 ways you can improve the business forecast for the next 6 months to a year. Test your suggestions by preparing revised budget schedule. Attached is an example of a budget report.
INFORMATION FOR IMAGINE, INC. BUDGET PROJECT
1.Imagine, Inc. is a company that re-sells one product, a particularly comfortable lawn chair.An overseas contractor makes the product exclusively for Imagine, so Imagine has no manufacturing-related costs.
2.As of 11/19, each lawn chair costs Imagine $4 per unit.Imagine sells each chair for $10 per unit.
3.The estimated sales (in units) are as follows:
\fSalaries and Wages $3,000 Sales Commissions 7% of sales revenue Rent $8,000 Other Variable Cash Expenses 6% of sales revenue Supplies Expense: See note $2,000 Other: See note $48,000 OCO VOUT A W N Imagine, Inc. Sales Budget For the 6 months ending June 2020 Nov '19 Dec '19 Jan '20 Feb '20 Mar '20 Apr '20 May '20 Jun '20 6 mos total Budged unit sales Selling price per unit 10 Total Sales 11 12 Cash Sales % 13 Credit Sales % 14 15 Cash Sales 16 Credit Sales 17 Total Sales 18 19 20 Current month A/R Collections 21 1 month prior A/R Collections 22 2 months prior A/R Collections 23 Uncollectible 24AutoSave OFF ACTG1C_Benson_Budget Project Template Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Arial 10 AA = Wrap Text General Insert v Ex Delete Ex AY- O. v Z Paste BIUV DVAv E Merge & Center v $ ~ % " Conditional Format Cell Format v Sort & Find & Ideas Sensitivity Formatting as Table Styles Filter Select A129 X V fx Net Income A B C D E F G H C 26 Cash Collections 27 For the 6 months ending June 2020 28 29 Jan '20 Feb '20 Mar '20 Apr '20 May '20 Jun '20 6 mos total 30 Current month cash Sales 31 Current month A/R Collections 32 1 month prior A/R Collections 33 2 months prior A/R Collections 34 Total cash collections 35 36 Bad Debt Expense 37 38 Desired ending inventory % 39 40 Imagine, Inc. 41 Purchase Budget 42 For the 6 months ending June 2020 43 44 Nov '19 Dec '19 Jan '20 Feb '20 Mar '20 Apr '20 May '20 Jun '20 6 mos total 45 Budged unit sales 46 Add desired ending inventory 47 Total needs 48 Less Beginning Inventory 49 Required Purchases 50 51 Cost per unit 52 Purchases 53 54 % Paid in Month of Purchase 55 % Paid in Month after Purchase 56 57 Budget FIFO Calculation + + 150%8 889 Imagine, Inc. Schedule of Budgeted Cash Disbursements for Merchandise Purchases For the 6 months ending June 2020 62 Jan '20 Feb '20 Mar '20 Apr '20 May '20 Jun '20 6 mos total 63 Cash purchases 64 1 month prior A/P Collections 65 Cash disbursements for merchandise purch 66 67 68 Fixed Operating expenses: Variable Operating Expenses: 69 Salaries and Wages Sales Commissions, % of Revenue 70 Rent Other Variable Cash Expenses, % of Revenue 71 Supplies Expense 72 Other - Overhead 73 Other - Depreciation 7474 75 Imagine, Inc. 76 Operating Expense Budget 77 For the 6 months ending June 2020 78 79 Jan '20 Feb '20 Mar '20 Apr '20 May '20 Jun '20 6 mos total 80 Salaries and Wages 81 Sales Commissions 82 Rent 83 Other Variable Cash Expenses 84 Supplies Expense 85 Other - Overhead 86 Other - Depreciation 87 Bad Debt Expense 88 Total operating expenses 89 Depreciation and noncash items 90 Bad Debt Expense 91 Cash disbursements for operating expenses 92 93 Equipment payment - January 94 Equipment payment - February 95 Dividends - March 96 Minimum Monthly Cash Budget 97AutoSave OFF ACTG1C_Benson_Budget Project Template Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Arial 10 AA = ab Wrap Text v General Insert v Ex AY- O. Paste BIUV DVAv E = Ex Delete Merge & Center v $ ~ % " Conditional Format Cell Find & Formatting as Table Styles Format v Sort & Ideas Sensitivity Filter Select A129 X V fx Net Income A B C D E F G H K 96 Minimum Monthly Cash Budget 97 98 Imagine, Inc. 99 Cash Budget 100 For the 6 months ending June 2020 101 102 Jan '20 Feb '20 Mar '20 Apr '20 May '20 Jun '20 6 mos total 103 Cash balance, beginning 104 Add collections from customers 105 Total cash available 106 Less disbursements: 107 Cash disbursements for merchandise purch 108 Cash disbursements for operating expenses 109 Equipment purchases 110 Dividends 111 Total cash disbursements 112 Excess of receipts over disbursements 113 Financing: 114 Borrowing-note 115 Repayments-note 116 Total financing 117 Cash balance, ending 118 119 120 Imagine, Inc. 121 Budgeted Income Statement 122 For the 6 months ending June 2020 123 124 Jan '20 Feb '20 Mar '20 Apr '20 May '20 Jun '20 Total 125 Sales, net 126 Cost of goods sold: 127 Gross margin 128 Total operating expenses 129 Net Income 130 131 Imagine Inc. Budget FIFO Calculation + + 140%AutoSave OFF ACTG1C_Benson_Budget Project Template Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Insert v Arial v 10 AA = ab Wrap Text General Ex Delete Ex AY- O. v Z Paste BIUV DVAv E = = Merge & Center v $ ~ % " Conditional Format Cell Sort & Find & Ideas Sensitivity Formatting as Table Styles Format v Filter Select A129 X V fx Net Income A B C D E F G H K 130 131 Imagine, Inc. 132 Budgeted Balance Sheet 133 6/30/20 134 135 Assets 136 Current Assets: 137 Cash 138 Accounts receivable 139 Supplies Merchandise Inventory 141 Plant and Equipment: 142 Buildings and Equipment 143 Accumulated Depreciation 144 Total assets 145 146 Liabilities and Equity 147 Accounts payable 148 Capital stock 149 Retained earnings 150 Total liabilities and equity 151 152 153 154 155 156 157 158 159 160 161 162 163 164 Budget FIFO Calculation + + 140%AutoSave OFF ACTG1C_Benson_Budget Project Template Q Share Comments Home Insert Draw Page Layout Formulas Data Review View Tell me Arial v 10 AA ab Wrap Text General Insert v Ex Delete Ex AY- O. Conditional Format Cell Sort & Find & Ideas Sensitivity Paste BIUV DVAv Merge & Center v $ ~ % 9 Formatting as Table Styles Format v Filter Select B34 fx A B C D E F G H K L M N P Q R S T U V W X -OOO VO UI A WN - FIFO Calculation Beg Beg Inventory - Purchases COGS Ending Ending Inventory Inventory Units Purchases Units COGS Units Inventory Units Jan Feb Mar Apr May 11 Jun 12 13 14 15 16 17 18 19 20 30 32 33 34 1 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 Budget FIFO Calculation + + 100%
