Objective: To create a master budget with supporting schedules using Excel.

Scenario: GC Electronics, Inc. re-sells just one product, a computer tablet. An overseas contractor makes the product exclusively for GC Electronics, so GC Electronics has no manufacturing-related costs. GC Electronics just finished their first year of business. The company has asked you to help prepare their master budget for the upcoming year.

Each computer tablet costs GC Electronics $200 to purchase. The current selling price is $250 per tablet.

Based on sales forecasts, GC Electronics expects the following unit sales for the upcoming year:

Month	Unit Sales
January	1,000
February	1,700
March	1,200
April	1,500
May	1,650
June	1,600
July	1,400
August	1,500
September	1,700
October	2,000
November	2,100
December	2,300

Note that GC Electronics expects sales to increase substantially during the holiday season. Unit sales in January 2023 is expected to be 1,100 tablets.

Thirty percent of any months sales are for cash, and the remaining 70% are on credit. Twenty percent of the credit sales are collected in the month of sale, 60% are collected in the following month, and the remaining 20% are collected in the second month after the sale.

GC Electronics has a minimum ending inventory policy equal to 20% of the next months cost of goods sold. All purchases are made on account. Thirty percent of the inventory purchases are paid for in the month of purchase and the remaining 70% are paid in the following month.

Monthly operating expenses consist of the following:

ITEM	Amount
Salaries and wages expense	$14,000
Sales commissions expense	5% of sales revenue
Rent expense	$10,000
Utilities expense	$1,500
Supplies expense	1% of sales revenue
Depreciation expense	Straight-line (see info below)
Miscellaneous expenses	$1,000

All expenses are paid in the month incurred except for sales commissions and utilities, which are paid in the following month.

Currently, GC Electronics has no fixed assets. The companys capital expenditure budget indicates that GC Electronics will spend $95,000 on January 1 for store fixtures. The fixtures are expected to have a $5,000 salvage value and a five-year (60-month) useful life. GC Electronics depreciates its assets using the straight-line method.

GC Electronics must maintain a minimum cash balance of $15,000. The company borrows funds and repays them in the exact amount needed on the last day of the month. Interest of 1.5% per month is paid in cash on the last day of the month. No line of credit liability exists as of December 31, 2021.

Collection of the current accounts receivable balance is expected as follows:

$60,000 in January from sales in November 2021

$190,000 in January from sales in December 2021

$30,000 in February from sales in December 2021

Requirements:

Prepare a comprehensive 12-month budget for the year 2022 with annual totals in Excel that includes the following:

Sales budget

Inventory purchases budget

Selling and administrative expense budget

Schedule of cash receipts

Schedule of cash payments for inventory purchases

Schedule of cash payments for selling and administrative expenses

Cash budget

Pro forma income statement for the year ended December 31, 2022

Pro forma balance sheet as of December 31, 2022