You are helping Bandit's Magic Shop with their annual budgeting process.You need to create a master budget for the company's 2018, first quarter's operations (January, February, March).This budget is for potential investors (friends of Bandit), whom he hopes will contribute capital to his business. Bandit's sells magic trick kits.

1. The following is information from the November and December budgets:

November

December

Credit Sales

$210,000

$253000

Supplies

$6,300

$7,590

Payroll

$14,500

$14,500

Misc

$10,500

$12650

Inventory Purchases

$196,000

$210,000

Ending Inv

700 magic kits

800 magic kits

1. You have obtained estimates for a sales forecast from Bandit, the store manager and the head sales clerk. (note these are three separate forecast, do not add them together)

Sales forecast for January (units)

Head Sales Clerk8,000

Bandit9,500

Store Manager7,500

Expectations are for sales to increase 5% in February and decrease 2% in March (compared to February sales).Round to nearest ten units for sales units. The kits sell for $50 each.

1. Typically 30% of sales are made in cash and 70% are in credit.Bandit's expects to collect credit sales as follows: 10% in the month of the sale, 60% in the month following the sale, and 30% two months following the sale.Assume there will not be any bad debt.

1. Bandit's would like to maintain an ending inventory balance of 10% of next month's sales.Each kit cost $28.00 to buy wholesale.They purchase all inventory on account.They pay 60% of their purchases in the month of purchase and 40% the month after purchase.April sales are budgeted to be 9,000 units.

1. They will have the following expenses
2. Insurance - $200 per month
3. Licensing fees - $160 per month
4. Supplies - 2% of total monthly sales
5. Advertising - $1250 per month
6. Payroll expense - $14,500 per month
7. Miscellaneous expenses - 4.5% of total monthly sales

1. Bandit's has decided that they need a new computer system and expects to buy it the beginning of January. The equipment will cost $10,000 and will be paid in cash.The computer system has a useful life of 5 years and salvage value of $250.They will begin depreciating the computer system in January. (Straight line depreciation).

1. They pay insurance, licensing, and advertising in the month incurred; they pay supplies, payroll, and miscellaneous expense in the month after incurred.

1. At the end of December, Bandit's expects to have debt of $25,000 outstanding. Currently the payment schedule is $2500 a month in principle and $100 a month in interest.

1. Ending cash balance for December is budgeted to be $145,000.

1. December's Retained earnings balance is $314,360

Required:

1. Open' BudgetProjectTemplate' from the assignment tool on Blackboard
2. Input the information given above into the "Assumption" worksheet
3. Link the appropriate information from the "Assumption" sheet to the appropriate cells in the various operating and financial budgets

Sales budget

Cash Receipts Budget

Inventory Purchases Budget

Cash Payments for Inventory Budget

Selling and Administrative Expense Budget

Cash Payments for Selling and Administrative Expense Budget

Cash Budget

Pro Forma Income Statement

\fForecast Used Enter formulas in the highlighted areas 1st Qrt Sales Budget Jan Feb March Sales BudgetCheck Unit Sales | '! ' Price per unit Total Sales ' Cash Receipts Cash Sales ' Credit Sales Collections Current month One month previous Two months previous Total Cash Receipts Total Cash Receipts - Check \fA B C D F M Enter females in shaded area, link to correct cells and worksheets Selling and Administrative Budget Jan Feb March Quarter totals check Insurance I II Licensing Fees Supplies Advertising Payroll expense for temp staff Miscellaneous Depreciation Total S&A (DCD'QOIUII-P-WMk LL AC) _L_L LON) Cash Payment SSA Insurance Licensing Fees Supplies Advertising Payroll expense for temp staff Miscellaneous Total S&A Cash Payments Total SM Cash Payments Check MLlLLLL DDCD'MIO'IUI-lh- MM [0.x nn \fCom'O'DUI-P-DJML Pro fonna Income statement Sales Revenue Cost of Goods Sold Gross Margin Selling and Admin. Exp. Operating Income Interest Expense Net Income Enter formulas ln shaded area, link to correct cells and worksheets Check |:l d 01 Misc Pa able Debt Liabili Total Liabilities d a) $ 25,000.00 $ 25,000.00 d a.\" dd '0'\" B Retained Earnins $ 314,360.00 $ 339,360.00 Total Liabilities and Equity 329,360.00 ___ ___ 329,360.00 ___ BALANCE SHEET NOT IN BALANCE, check other bud ete for errors ___ ___ ___ 1_ 2 ___ 3 ___ 4 ___ 5 ___ 6 \"___ 7 ___ 8 ___ 9 ___ 1o ___ 11_ 12 Accounts Pa able __ 13 SUHliesPa able __ 14 Pa rellPa able __ __ __ ___ __ __ __ emerge 0M 3'14